[eluser]aidehua[/eluser]
I have two database tables. For simplicity's sake, let's call them tbProducts and tbProduct_images
Code:
tbProducts
----------
id |name
================
1 |abc
2 |def
3 |xyz
Code:
tbProduct_images
-----------------
prod_id |img_name |is_primary
=============================
1 |foo.jpg |TRUE
1 |boo.jpg |FALSE
1 |doo.jpg |FALSE
2 |waa.jpg |TRUE
2 |waa.jpg |FALSE
3 |gee.jpg |FALSE
3 |wee.jpg |FALSE
So, lots of products, and each product can have one or more associated images. In theory all products should have one (and no more than one) image set as a primary image. However, in practice (yes, upstream application design fault...) some products do not have an image set as their primary image (product #3, in the example I've given above - both its images have is_primary set to FALSE).
I want to write, if possible, a single SQL query that will return ALL my products along with their matching primary image name (if they have one). But I don't want to exclude products that don't have a matching primary image.
Here's what I want to return:
Code:
prod_id |name |img_name
=============================
1 |abc |foo.jpg
2 |def |waa.jpg
3 |xyz |NULL
Here's the SQL I've tried:
Code:
SELECT tbProducts.id, tbProducts.name, tbProduct_images.img_name
FROM tbProducts
LEFT JOIN tbProduct_images ON tbProducts.id = tbProduct_images.prod_id
WHERE tbProduct_images.is_primary = TRUE
But that doesn't quite work because it excludes product #3 because it doesn't have a matching primary image - i.e. it returns only this:
Code:
prod_id |name |img_name
=============================
1 |abc |foo.jpg
2 |def |waa.jpg
Can anyone give me a query that would return all three products, but with a NULL value for the img_name where a product has no primary image?
Thanks
Ed
