Bobface Bobface - 10 days ago 6
MySQL Question

Return value even if an entry does not exist

I am using the following query:

SELECT shop_entrys.id, shop_images.path FROM shop_entrys,shop_images
WHERE shop_entrys.id = shop_images.s_id AND
shop_images.pos = 0 AND
shop_entrys.category_id = 1
ORDER BY shop_entrys.pos ASC


However, a row in shop_entrys might exist with no link to a row in shop_images. Thus, ... WHERE shop_entrys.id = shop_images.s_id ... will not be met. In this case, I would still like to return a result. For example:

shop_entrys.id shop_images.path
1 "/img1.jpg"
... ...
42 "not found"


How should I change the above query to still return a result?

Answer

Using a LEFT OUTER JOIN with COALESCE to give a default value to the 2nd column

SELECT shop_entrys.id, COALESCE(shop_images.path, 'NOT FOUND' )
FROM shop_entrys
LEFT OUTER JOIN shop_images 
ON shop_entrys.id = shop_images.s_id AND shop_images.pos = 0 
WHERE shop_entrys.category_id = 1 
ORDER BY shop_entrys.pos ASC