[El Forum]

[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?