CODEWITHSUNDEEP
mysql
JohnBigs
JohnBigs

Reputation: 2811

how to get result of join also for records that dont have a joined record with another table?

I have this query:

SELECT suppliers.id, count(*) 
FROM suppliers
INNER JOIN supplier_addresses 
ON suppliers.id = supplier_addresses.supplier_id 
GROUP BY suppliers.id;

this gives my a table of supplierId and count of its addresses in the supplier_addresses table. But it only shows me suppliers that have at least 1 address.

I want to see in the result also count of 0 addresses...for example:

supplier.id | count(*)
1             3
2             0
3             1
4             9

in my query I dont see the second record.

Upvotes: 0

Views: 24

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

Use LEFT JOIN

SELECT suppliers.id, count(supplier_addresses.supplier_id ) 
FROM suppliers
LEFT JOIN supplier_addresses 
ON suppliers.id = supplier_addresses.supplier_id 
GROUP BY suppliers.id;

Upvotes: 1

Related Questions