SQL wizard needed: join two tables without excluding "missing" records from right-hand table

[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

[eluser]aidehua[/eluser]
Funny how taking the time to write out the question in this forum can sometimes help you see the light...

I've cracked it (I think). This seems to be doing what I want:

Code:

SELECT tbProducts.id, tbProducts.name, tbProduct_images.img_name
FROM tbProducts
LEFT JOIN tbProduct_images
ON tbProducts.id = tbProduct_images.prod_id
AND tbProduct_images.is_primary = TRUE

i.e. the qualifier "tbProduct_images.is_primary = TRUE" goes in the ON part of the JOIN, and not in a WHERE clause at the end.

Any SQL Kings or Queens out there care to comment on whether this is the right way to go about it?

Oh - and a follow-up question for a bonus point: what if I decide that I want to get the primary image for each product, if there is one, or, if there isn't one, then just get the first image in tbProduct_images for that particular image. Can I do that in a single query?