[SOLVED] Help with Query...

[thepip3r]

SELECT q.quote, qp.paraphrase, s.*, u.username
FROM quotes q, quotes_paraphrases qp, sources s, users u
WHERE q.id=qp.quote_id
AND q.source_id=s.id
AND q.user_id=u.id
AND q.id=qp.quote_id
AND q.enabled=1

 

so the problem is that I'm trying to pull all of the quotes out of the quotes table and include the pararphrases if a paraphrase has been entered for each particular quote.  Unfortunately, right now, this query only pulls quotes that HAVE paraphrases and leaves out the majority.  Is there a way I can restructure this query so that it will just leave my paraphrase fields blank for the quotes that do not have corresponding paraphrases?  All help is greatly appreciated!

[fenway]

You need to use LEFT JOINs for this:

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

[corbin]

Instead of selecting from multiple tables and making the columns match up, you should do a LEFT JOIN.  (A LEFT JOIN will show entries from the table on the left even if no matching entry exists on the right.)

 

 

SELECT <blah> FROM quotes q LEFT JOIN quotes_paraphrases qp ON qp.quote_id = q.id JOIN sources s ON s.id = q.source_id JOIN users u ON u.id = q.user_id WHERE q.enabled = 1;

 

 

Edit:  Blerh, fenway beat me.  Beat me by 3 minutes x.x. I should stop getting distracted so easily.

[thepip3r]

Thank you Si-Fu.  One more question in the same query:

 

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

 

I guess this is a three part question.  I have all of my table primary keys titled just "id" so it's easy to reference table.id.  In this query, the quote.id and sources.id fields are represented in the result set but both entitled "id". 

 

1)  Should I rename my primary keys unique across my entire DB?

2)  If not, is there a way to uniquely identify the different id fields returned in the result set?

3)  Or if not, is there a way to use and asterisk(*) on a table but exclude one or more fields?

 

Pseudocode example of 3:

SELECT q.quote, q.id, qp.paraphrase, s.*(-s.id), u.username

[corbin]

1.  I usually make my primary keys something meaning full.  user_id, post_id, so on for example, not just "id."

 

2.  You can use aliases.  SELECT table1.col1 AS SomeName, table2.col1 as SomeOtherName;

 

3.  No.  You'll have to type them all out manually ;p.

[thepip3r]

Thanx for the reply corbin.  Aside from having to add the AS keyword in my queries for clarity, is there any other functional reason why I should not leave them ambiguous?  I'm asking because I'll change if I have to but am lazy and don't want to go through my entire db to change the fields around. =P

[thepip3r]

eh screw it.  thanx for the assistance guys, i'll go through and rename my db keys.