Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Jul 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    displaying all the fields in a table that have a certain field in common...

    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?

  • #2
    Regular Coder
    Join Date
    Jun 2002
    Posts
    358
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Can tell more detail on what u want to display?
    Thanks.
    =====================================================
    From NinjaTurtle
    ++http://ohmygoh.blogspot.com|http://technology.ohmygoh.com++

  • #3
    Regular Coder
    Join Date
    Dec 2003
    Posts
    367
    Thanks
    0
    Thanked 0 Times in 0 Posts
    select * from Users where UserID=1 or LastIP = (select LastIP from Users where UserID = 1)

    shmoove

  • #4
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    Code:
    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.
    Check out the Forum Search. It's the short path to getting great results from this forum.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •