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

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?

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

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


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.

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.