sql - Proper Syntax for 3 table SELECT query -

i've got 3 tables:

• tblposts
• tblcomments
• tblusers

i'm trying listing of posts along associated comments. tricky part seems getting posts , comments show proper author (user). closest posts authors incorrect. i'm grouping cfoutput on "pid", each post 1 time expect.

select tblposts.pid        , tblposts.title        , tblposts.description        , tblposts.price        , tblposts.datecreated pdate        , tblposts.image1        , tblcomments.comment        , tblcomments.datecreated cdate        , tblusers.fname        , tblusers.lname   tblposts           left join tblcomments on tblposts.pid = tblcomments.pid           left join tblusers on tblcomments.uid = tblusers.uid 

any thoughts? thanks!

since both tables contain author id, must join tbluser twice: once posts , once comments. means must use table alias differentiate between two. along these lines, pa alias "post author" , ca alias "comment author".

select p.pid        , p.title          , ...         , pa.fname postauthorfirstname        , pa.lname postauthorlastname        , ca.fname commentauthorfirstname        , ca.lname commentauthorlastname tblposts p          left join tblusers pa on pa.uid = p.uid         left join tblcomments c on p.pid = c.pid         left join tblusers ca on ca.uid = c.uid