use foreach or single query? (Counting Inventory)

use foreach or single query? (Counting Inventory)

El Forum
Guest

#14

01-22-2010, 04:46 PM

[eluser]danmontgomery[/eluser]
Ah, I think I get it now.

You can get the location ID first, then add it to the join statement instead of using WHERE:

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 AND location_id = 2 )

GROUP BY products.id

Quote:qty product_id product_name
1 1 Product 1
0 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