Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Posts
    3

    Question Unanswered: double inner join?

    Hi,

    I would like to take the following sql and modify it to be able to grab two usernames from the USER table instead of one like now

    right now, USERNAME gets set based on a match between USER.UID and CP.lpUIDuser...
    I would also like to match USER.UID and CP.userUID and extract this username.. not quite sure how to do this though.

    Code:
    SELECT CP.UID, CP.parentUID, CP.userUID, CP.iniTIMESTAMP, CP.liveTIMESTAMP, CP.TYPE, CP.TITLE, CP.webTITLE, CP.DESCRIPTION, CP.viewCOUNT, CP.replyCOUNT, CP.upVOTE, CP.downVOTE, CP.customVAL1, CP.lpTIMESTAMP, CP.lpUIDuser, USER.UID, USER.USERNAME FROM CP INNER JOIN USER ON USER.UID = CP.lpUIDuser ORDER BY CP.lpTIMESTAMP DESC LIMIT 0, 15
    Thanks for any help!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You need to use aliases to allow you to relate to two different rows in one table within a single SQL statement.
    Code:
    SELECT CP.UID, CP.parentUID, CP.userUID
    ,  CP.iniTIMESTAMP, CP.liveTIMESTAMP, CP.TYPE
    ,  CP.TITLE, CP.webTITLE, CP.DESCRIPTION
    ,  CP.viewCOUNT, CP.replyCOUNT, CP.upVOTE
    ,  CP.downVOTE, CP.customVAL1, CP.lpTIMESTAMP
    ,  CP.lpUIDuser, A.UID, A.USERNAME
    ,  B.UID, B.USERNAME
       FROM CP
       INNER JOIN USER AS A
          ON A.UID = CP.lpUIDuser
       INNER JOIN USER AS B
          ON B.UID = CP.userUID
       ORDER BY CP.lpTIMESTAMP DESC 
       LIMIT 0, 15
    -PatP

  3. #3
    Join Date
    Jan 2009
    Posts
    3

    Question

    Thanks! code works great..but again I have the ambiguity problem when actually displaying the rows (in php)
    how can I distinguish between A and B? it won't let me do $CP['A.USERNAME'], only $CP['USERNAME']

  4. #4
    Join Date
    Jan 2009
    Posts
    3
    ahhhh i got it.. just needed two more AS statements

    thanks!

Posting Permissions

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