...

View Full Version : displaying all the fields in a table that have a certain field in common...



Ricapar9x
07-13-2004, 03:29 AM
Im coding a message board in ASP, and some users have more than one account. What would be the query/queries to display all the entires in a table that have the 'LastIP' field in common?

im thinking of this:

rs.Open "SELECT UserName,LastIP FROM Users WHERE UserID = 1"
rs2.Open "SELECT UserName FROM Users WHERE LastIP = rs.Fields(1)"

Would that work? If it wont, what will?

NinjaTurtle
07-13-2004, 09:46 AM
Can tell more detail on what u want to display?

shmoove
07-13-2004, 11:36 AM
select * from Users where UserID=1 or LastIP = (select LastIP from Users where UserID = 1)

shmoove

Roy Sinclair
07-13-2004, 08:29 PM
You don't say what the "userid = 1" condition is supposed to be doing for you, I'm going to assume it's not relevant though it wouldn't be difficult to add it to what I'm going to suggest.

You also didn't mention the database so I'm also going to assume you aren't using something lame like MS Access because your board can't use that and be serious in it's scope.



select
U1.UserName
,U1.LastIP
from Users U1
Left join Users U2 on U1.LastIP = U2.LastIP
Where U2.Username is not null and U2.UserName <> U1.UserName


That's the basic statement I would start with. It would help you a lot if you use a tool like MS Query Analyzer to run the query and tune it until you get the results you expect and then merge it into your APS page.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum