Is there a way to join the same table twice in a single selection?

[xvszero]

This seems like it should be such an easy thing to do, but I'm a bit stumped. Basically I have a forum and on the main forum page I have a link to the thread, the user who posted it, date/time posted, etc. Typical forum stuff. In the database the post stuff is stored in parentmessages and is joined to the userinfo table on userid to get the poster's username, user font colors, user type, etc.

(I stripped it down here to the basics just to show how it is joined...)

SELECT parentmessages.*, userinfo.username AS username FROM parentmessages, userinfo WHERE parentmessages.userid = userinfo.userid

Now, I also have a field in the parentmessages table called lastreplyuser that saves the userid of the person who has replied to the thread last. I want to join to the userinfo table to get their username, font colors, etc. and post it on the main forum page for each thread, but I'm already joined to the userinfo table to get the user who posted the thread's info.

I'm not even sure if this is possible. Maybe with a temp table that is essentially the userinfo table? I dunno.

Help?

[xvszero]

I'm trying something like this...


SELECT parentmessages.*, mainuser.username, mainuser.userlevel, mainuser.userxp, mainuser.useravatar,
mainuser.userdisptextcolor, mainuser.userdispbgcolor, mainuser.usertype, mainuser.refid FROM parentmessages

LEFT OUTER JOIN (SELECT userid, username, level AS userlevel, xp as userxp, avatar as useravatar, disptextcolor as userdisptextcolor,
dispbgcolor as userdispbgcolor, usertype, refid FROM userinfo) mainuser
ON (parentmessages.userid = mainuser.userid)

LEFT OUTER JOIN (SELECT userid as replyuserid, username as replyusername, disptextcolor as replydisptextcolor,
dispbgcolor as replydispbgcolor, usertype as replyusertype FROM userinfo) lastreplyuser
ON (parentmessages.lastreplyuser = lastreplyuser.replyuserid)

WHERE sticky='yes' AND active='yes' AND ((parentmessages.level <= '$boardlevelstarttemp' AND parentmessages.level >= '$boardlevelendtemp' AND parentmessages.level >= '$userlevel')
OR parentmessages.level=99) ORDER BY lastreplycalc DESC


And it works fine as long as I do one or the other of the joins, but it dies when I try to do both and gives me this error message...

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

[xvszero]

@@max_join_size
7000000

Not exactly sure what that means though, or why my join would be exceeding it...

[lionfan1991]

SELECT parentmessages.*, ui1.username AS username, ui2.username AS lastreplyname
FROM parentmessages pm LEFT JOIN userinfo ui1 ON pm.userid = ui1.userid
LEFT JOIN userinfo ui2 ON pm.lastreplyuser = ui2.userid