[eluser]danmontgomery[/eluser]
SQL looks correct, you're almost certainly being limited by the WHERE statement... If there are only 3 rows in inventory, you are joining location on inventory, then limiting location, so you have a maximum of 3 rows with that query... If you remove the WHERE you should see all products... Just confirmed this locally. (IFNULL is unnecessary with COUNT(), which returns 0 for NULL)
Code:
SELECT COUNT(inventory.serial_no) AS qty, products.id AS product_id, products.name AS product_name
FROM products
LEFT JOIN inventory ON inventory.product_id = products.id
LEFT JOIN locations ON locations.id = inventory.location_id
GROUP BY products.id
Quote:qty product_id product_name
2 1 Product 1
1 2 Product 2
0 3 Product 3
0 4 Product 4
0 5 Product 5
0 6 Product 6