MySQL Subqueries / Joins

I have two different tables, products and inventory.

Basically, what I'm trying to accomplish is I need to loop thru every row in my products table and reference the corresponding rows in the inventory table.

My goal is to find out what products have a quantity of 0.

The inventory table has multiple rows for each product id. The problem I'm facing is that ALL inventory rows that correspond to a particular product need to have a quantity of 0 for the result to be of any use to me (this is what I'm trying to figure out)

Right now, I'm just looping thru every product and then executing a second SQL statement that checks for any occurrences of quantity > 0 in the inventory table for the selected product, but that doesn't work with my pagination script (20k rows in the product table, many more in the inventory table. displaying all at once is n开发者_高级运维ot an option)

You should just preform an inner-join between the two tables and then select where quantity == 0.

Something like:

select
    products.*
from
    products
where
    (select sum(inventory.quantity) from inventory where products.id = inventory.productId) = 0

pseudo code

The query becomes.

SELECT p.*, SUM(i.qty,0) as number_in_stock
FROM product p
INNER JOIN inventory i ON (i.product_id = p.id)
GROUP BY p.id
HAVING number_in_stock = 0

This will be much slower though, because having only works after all the joining has been done.

If you remove the inventory-line from the table when qty becomes 0, you can do this query, which is much faster:

SELECT p.*
FROM product p
LEFT JOIN inventory i ON (i.product_id = p.id)
WHERE i.id IS NULL

A left join does this in the shoe store

product id name           inventory id prod_id qty
------------------        -------------------
        1  shoe                      1 1       10
        2  horse                     We have no horses, all nulls
        3  boot                      2 3       55
        4  ferrari                   go to the cardealer, all nulls

Now all we need to do is select all rows that have null in a field for inventory that cannot normally be null. Aha, the primary key can never be null, so if we select all rows where the inventory PK is null, we will have the products that are not in the inventory.