[SOLVED] JOINing dilemma -- pls assist where possible.

[thepip3r]

SELECT q.quote, q.qid, qp.paraphrase, s.*, u.username
FROM quotes q
LEFT JOIN quotes_paraphrases qp ON ( q.qid=qp.quote_id )
LEFT JOIN sources s ON ( q.source_id=s.sid )
LEFT JOIN users u ON ( q.user_id=u.uid )
WHERE q.enabled=1
AND q.qid=$rand

 

my problem is that i have a numeric field in both the quotes table and the quotes_paraphrases table that are called user_id.  The problem is that the person who submitted the quote and the person who submitted the paraphrase of that quote are not always the same.  Is there a way to modify this SQL statement so that I can pull both the quotes.user_id=users.uid AND quotes_paraphrases.user_id=users.uid?

 

I've tried inter-mixing an INNER join in the statement above but that gave me syntax errors (had to try), I've tried just amending the last LEFT JOIN to look like: (LEFT JOIN users u ON ( q.user_id=u.uid AND qp.user_id=u.uid) but that just adds a bunch of NULL values to the column of the result set that used to hold the user who submitted the quote where there wasn't an associated paraphrase to that particular quote. 

 

All help is greatly appreciated.

[corbin]

SELECT q.quote, q.qid, qp.paraphrase, s.*, qu.username AS quote_username, qpu.username AS para_username

FROM quotes q

LEFT JOIN quotes_paraphrases qp ON ( q.qid=qp.quote_id )

LEFT JOIN sources s ON ( q.source_id=s.sid )

LEFT JOIN users qu ON ( q.user_id=qu.uid )

LEFT JOIN users qpu ON ( q.user_id=qpu.uid )

WHERE q.enabled=1

AND q.qid=$rand

[thepip3r]

damnit corbin... sorry for asking help on the same query two times in the same day.  thanks for helping me both times -- at least you were familiar with the topic.  =P

 

thanx again.

[corbin]

No problem.