trouble creating a sql statement select statement reference same table twice

Question

Ok, here is setup. Note the code below is SIMPLIFIED version.

PHP 5.2

ENGINE: MyISAM

table quotes q   
table users u   

FROM quotes q LEFT JOIN users u
ON q.qid = u.uid

Soo... the quotes table references a user (from users table) (the owner of quote) but quotes table ALSO has a field called createdby (the user who created the quote...)

I'm trying to display quote #, the owners full name, and the created by full name.

I can display the quote #, owners full name and created by USERNAME (i want full name) using the above code.

I tried adding

ON q.qid = u.uid     + -->    AND q.createdBy = u.uid

But that didn't seem to work and even if it did I wouldn't know how to reference the correct full name (owner or createdby).

Answer 1

SELECT  q.id, owner.fullname, creator.fullname
FROM    quotes q
LEFT JOIN
        users owner
ON      owner.uid = q.qid
LEFT JOIN
        users creator
ON      creator.uid = q.createdby