Welcome Guest, Not a member yet? Register   Sign In
use foreach or single query? (Counting Inventory)
#12

[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


Messages In This Thread
use foreach or single query? (Counting Inventory) - by El Forum - 01-22-2010, 07:21 AM
use foreach or single query? (Counting Inventory) - by El Forum - 01-22-2010, 07:35 AM
use foreach or single query? (Counting Inventory) - by El Forum - 01-22-2010, 07:49 AM
use foreach or single query? (Counting Inventory) - by El Forum - 01-22-2010, 09:05 AM
use foreach or single query? (Counting Inventory) - by El Forum - 01-22-2010, 09:29 AM
use foreach or single query? (Counting Inventory) - by El Forum - 01-22-2010, 10:24 AM
use foreach or single query? (Counting Inventory) - by El Forum - 01-22-2010, 11:07 AM
use foreach or single query? (Counting Inventory) - by El Forum - 01-22-2010, 12:20 PM
use foreach or single query? (Counting Inventory) - by El Forum - 01-22-2010, 01:54 PM
use foreach or single query? (Counting Inventory) - by El Forum - 01-22-2010, 02:02 PM
use foreach or single query? (Counting Inventory) - by El Forum - 01-22-2010, 03:43 PM
use foreach or single query? (Counting Inventory) - by El Forum - 01-22-2010, 04:05 PM
use foreach or single query? (Counting Inventory) - by El Forum - 01-22-2010, 04:43 PM
use foreach or single query? (Counting Inventory) - by El Forum - 01-22-2010, 04:46 PM
use foreach or single query? (Counting Inventory) - by El Forum - 01-22-2010, 08:21 PM



Theme © iAndrew 2016 - Forum software by © MyBB