Conditional JOIN Statement in MySQL

join, mysql, php, sql

Solution

Use `LEFT JOIN` instead. And `COALESCE` since some of the records where null (I guess). Try,

SELECT a.id id,a.price price,a.stock stock,
       a.max_per_user max_per_user,a.purchased purchased, 
       COALESCE(b.quantity, 0) owned 
FROM shop_items a 
          LEFT JOIN shop_inventory b 
                ON b.iid=a.id AND b.cid=a.cid 
WHERE a.cid=1 AND 
      a.szbid=0 AND 
      a.id IN(3,4)

Problem

I have the following, working MySQL query: ``` SELECT a.id id, a.price price, a.stock stock, a.max_per_user max_per_user, a.purchased purchased, b.quantity owned FROM shop_items a JOIN shop_inventory b ON b.iid=a.id AND b.cid=a.cid WHERE a.cid=1 AND a.szbid=0 AND a.id IN(3,4) ``` The `JOIN` joins the table `shop_inventory b` to return `b.quantity owned`. However, if there is no record in the `shop_inventory b` table where `b.iid=a.id` I want it to return `b.quantity = 0`. How would I do this?

Original source