[thelist] sql: LEFT JOIN and WHERE clause trouble
Paul Cowan
evolt at funkwit.com
Tue Aug 12 20:00:56 CDT 2003
Chris wrote:
> Notice on line 7 I have "pmed.type = 0". To say this in English it would
> be something like "Show me all records from the pmed table that have a 0
> in the type column." What I want it to actually say would be something
> like "Show me all records from the pmed table that have a 0 in the type
> column, or where no matching record exists at all between the
> relationship created by the INNER JOIN."
You need to move the pmed.type clause from the WHERE (applied *after*
the join is already done, if you catch my drift), to the JOIN itself
(where it's done as *part* of the join).
Lines 5-8 become:
5 LEFT JOIN products_media AS pmed
6 ON (pmed.prodid = p.id)
6a AND (pmed.type = 0)
7 WHERE
8 p.product_isactive = 1
Effectively, you're LEFT JOINing onto a 'subset' of pmed, which only
includes those with type = 0, rather than the whole table.
Hope this helps,
Paul
More information about the thelist
mailing list