Join same table twice ( SQL ) 1

[snowboardr]

I am having problems with my SQL I am basicly trying to get two "thread" names from the same table (thread) here is what i have...

sql= "SELECT "_
& "post.pid,"_
& "post.ptid,"_
& "post.ptid2,"_
& "post.puid,"_
& "post.psubject,"_
& "post.ptype,"_
& "post.preplycount,"_
& "post.ptime,"_
& "post.psticky,"_
& "post.ptimereply,"_
& "user.uid,"_
& "user.uname,"_
& "th.tid,"_
& "th.tname as ThreadName1,"_
& "th2.tid,"_
& "th2.tname as ThreadName2,"_
& " FROM forumpost as post, users as user, thread as th, thread as th2 LEFT join on user.uid=post.puid WHERE post.ptype=0 AND th.tid=post.ptid AND th2.tid=post.ptid2 Order by DESC,post.pid DESC Limit " & sLimitPart

 

[snowboardr]

nevermind... i got it!

For anyone who cares...

sql= "SELECT "_
& "post.pid,"_
& "post.ptid,"_
& "post.ptid2,"_
& "post.puid,"_
& "post.psubject,"_
& "post.ptype,"_
& "post.preplycount,"_
& "post.ptime,"_
& "post.psticky,"_
& "post.ptimereply,"_
& "user.uid,"_
& "user.uname,"_
& "th.tid,"_
& "th.tname as ThreadName1,"_
& "th2.tid,"_
& "th2.tname as ThreadName2"_
& " FROM forumpost as post, users as user INNER JOIN thread th ON th.tid=post.ptid INNER JOIN thread th2 on th2.tid=post.ptid2 WHERE post.ptype=0 AND user.uid=post.puid Order by post.pid DESC Limit " & sLimitPart

Site San Diego www.sitesd.com

 

[r937]

just a tip regarding this code --

FROM forumpost as post, users as user 
INNER JOIN thread th ON th.tid=post.ptid

as you can see, this mixes the "table list" and JOIN syntaxes

my advice: change this now to use JOIN syntax only

mixing them can (and will, in this particular instance) give you a syntax error in version 5

see

http://bugs.mysql.com/bug.php?id=13551

r937.com | rudy.ca

 

[snowboardr]

thanks for the tip! I would have never known this until MySQL was upgraded ha

Site San Diego www.sitesd.com