[thelist] sql: LEFT JOIN and WHERE clause trouble

[Chris W. Parker]

Hello,

I've got two tables: one contains products (called "products"), the
other (called "products_media") contains information regarding the media
that goes along with each product.

Here is a basic query: (with lines numbered)

1  SELECT p.id
2 	, p.name
3	, pmed.url
4  FROM products AS p
5  LEFT JOIN products_media AS pmed
6  	ON pmed.prodid = p.id
7  WHERE pmed.type = 0
8	AND p.product_isactive = 1

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."

I think that may be the wrong way to word it, so let me try it another
way.

Right now I have three products defined in the products table. Only two
of those products have records in the products_media table where the
column "type" equals 0.

When I do a query for all products in the database I'm only given the
two products that have corresponding records in the products_media
table. What I want to have returned to me is ALL the products in the
database whether or not they have a record in the products_media table.
*BUT* if they do in fact have a record in the products_media table it
must have a type of 0 and nothing else.

Does that make sense?

How can I accomplish this? Do I need to resort to my code and not rely
on SQL to perform this? Using code would be much easier but it just
seems like it would be a lot more expensive (processor time wise) than
having the SQL engine do it.


Thanks for your ideas.

Chris.

p.s. If I wasn't clear please let me know.