[thelist] SQL question

[Liam Delahunty]

Carl J Meyer wrote:
<quote>
I've used SQL for years in DB/web applications, and never have I ever
used any of the JOIN keywords (INNER,OUTER,LEFT,RIGHT,JOIN,etc).  I've
always done what appears to be exactly the same thing, just using the
WHERE clause (ie table1.key=table2.fkey).
...
Or are there things you can do with JOIN that you can't do with the WHERE
clause?
<end>

On a voting script the display results page needs to show all choices
including those that nobody has voted for. Data was in a choice_tbl and a
vote_count_tbl which had all the individual votes and rankings.

With a LEFT JOIN, all the matching fields from the left hand table are
returned, even if there are no corresponding entries in the right hand
table.

SELECT c.choice_name, c.id, SUM(v.vote_weight)
FROM choice_tbl c LEFT JOIN vote_count_tbl v ON (c.id = v.choice_id)
WHERE c.vote_id = $vote_id
GROUP BY c.choice_name, c.id

Cool beans.

Kind regards,
Liam Delahunty
http://www.corx.net/