Bobface Bobface - 10 months ago 42
MySQL Question

Return value even if an entry does not exist

I am using the following query:

SELECT, shop_images.path FROM shop_entrys,shop_images
WHERE = 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_images.s_id ... will not be met. In this case, I would still like to return a result. For example: shop_images.path
1 "/img1.jpg"
... ...
42 "not found"

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

Answer Source

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

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