Joining on two requirments?

First of all, I hope I can explain this properly. I have two tables

images
– image_id
– product_id
– file_name
– is_main_image

AND

products
– product_id
– name

Each product may have many images (related on product_id) but one of those images will be the ‘main’ image. In one query I would like to join the images table so I have the ‘main’ image in my results without having to do two queries. One cavaet is that not every product will have an image (much less a main image) so I still need the product returned with an empty image.

I’ve tried several different methods of JOIN I hope what I’m posting below conveys what I’m trying to do.


SELECT 
  products.product_id
, products.name
, images.file_name

FROM products

INNER JOIN images
  ON images.product_id = products.product_id 
  AND images.is_main_image = '1'

WHERE products.product_id = 'XXX'



I know the above is not right, I’ve been through several iterations of trying to do this and have been VERY unsuccessful lol.

Any guidance or assistance or push in the right direction will be greatly appreciated. Many thanks in advance!

Use LEFT OUTER JOIN instead of INNER JOIN and it should work

The problem with using the outer join is my AND within the JOIN (is that even possible?)

AND images.is_main_image = '1'

If a product doesn’t have a main image no result is returned.

Strange. This should work:


SELECT 
   products.product_id
 , products.name
 , images.file_name
FROM products
LEFT OUTER JOIN images
ON images.product_id = products.product_id 
AND images.is_main_image = '1'
WHERE products.product_id = 'XXX'

If it doesn’t, try this:


SELECT 
   products.product_id
 , products.name
 , images.file_name
FROM products
LEFT OUTER JOIN 
  (SELECT 
       product_id 
     , file_name
   FROM images
   WHERE is_main_image = '1'
  ) AS images
ON images.product_id = products.product_id 
WHERE products.product_id = 'XXX'

That worked like a charm! Many thanks!