phpmyadmin - mysql join 2 tables - show all rows from one table

Question

I have two tables, like so: table "a" contains:

id|name

stock1|fullname
stock2|fullname2
stock3|fullname3

table "b" contains product quantities for given stock.

id|stock_id|quantity|product_id|
1|stock1|3|13
2|stock3|4|13
3|stock1|1|5
4|stock2|2|2

Now I would need to combine those two tables, so that each product takes its stock full name from table "a", and if its quanitity is not given for stock, it would still show the row with the quanitity as 0.

So from my example, product_id 13 would show as:

stock|quanitity|product_id|stock_fullname
stock1|3|13|fullname1
stock2|0|13|fullname2
stock3|4|13|fullname3

Answer 1

You should be able to use a LEFT JOIN to achieve this.

SELECT a.id AS stock, COALESCE(b.quanitity,0), b.product_id, a.name AS stock_fullname
FROM a
LEFT JOIN b
  ON a.id = b.stock_id
  AND b.product_id = 13