[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