CODEWITHSUNDEEP
mysql
Bobface
Bobface

Reputation: 2952

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?

Upvotes: 2

Views: 41

Answers (2)

Kickstart
Kickstart

Reputation: 21513

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

Upvotes: 4

sagi
sagi

Reputation: 40471

Use a LEFT JOIN and COALESCE() :

SELECT shop_entrys.id,
        COALESCE(shop_images.path,'NOT FOUND') 
FROM shop_entrys
LEFT 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

Please avoid using implicit join syntax(comma separated) as it is deprecated and messy and a lot of times leads to mistakes. Use only the proper syntax of a join!

Upvotes: 3

Related Questions