[thelist] sql: LEFT JOIN and WHERE clause trouble

Ken Schaefer ken at adOpenStatic.com
Tue Aug 12 20:05:43 CDT 2003


I think this'll do what I think you want (if that makes sense!):

SELECT 
    p.id
   , p.name
   , pmed.url
FROM 
    products AS p
LEFT JOIN 
    products_media AS pmed
ON 
    pmed.prodid = p.id
AND
    pmed.type = 0
WHERE
   p.product_isactive = 1

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Chris W. Parker" <cparker at swatgear.com>
Subject: [thelist] sql: LEFT JOIN and WHERE clause trouble


<snip>

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.



More information about the thelist mailing list