double inner join?

stunnaman · #1 (**permalink**) 01-05-09, 14:01

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!

Pat Phelan · #2 (**permalink**) 01-05-09, 14:57

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

stunnaman · #3 (**permalink**) 01-05-09, 15:13

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']

stunnaman · #4 (**permalink**) 01-05-09, 15:15

ahhhh i got it.. just needed two more AS statements

thanks!

Internet Services

Web Development

Digital Marketing

Consumer Tech