Subquery and selecting oldest rows for multiple foreign keys in MySQL

Question

I have two tables:

product (idproduct, name, description, tax)

product_storage (idstorage, idproduct, added, quantity, price)

for each product it could be different price and oldest are "first-to-sell"

for example in storage I have:

1, 1, 2010-01-01,  0, 10.0
2, 1, 2010-01-02,  0, 11.0
3, 1, 2010-01-03, 10, 12.0
4, 2, 2010-01-04,  0, 12.0
5, 2, 2010-01-05, 10, 11.0
6, 2, 2010-01-06, 10, 13.0
7, 3, 2010-01-07, 10, 14.0
8, 3, 2010-01-08, 10, 16.0
9, 3, 2010-01-09, 10, 13.0

and now I need to select all products with current price, which is in the oldest row in product_storage where quantity > 0 for each product:

SELECT p.idproduct, p.name, p.tax,
       (SELECT s.price
        FROM product_storage s
        WHERE s.idproduct=p.idproduct AND s.quantity > 0
        ORDER BY s.added ASC
        LIMIT 1) AS price
FROM product p;

works fine, but it doesn't when I want to calculate price with tax in query:

SELECT p.idproduct, p.name, p.tax,
       (SELECT s.price
        FROM product_storage s
        WHERE s.idproduct=p.idproduct AND s.quantity > 0
        ORDER BY s.added ASC
        LIMIT 1) AS price,
        (price * (1 + tax/100)) AS price_with_tax
FROM product p;

MySQL says:

Unknown column 'price' in 'field list'

Update

Using subquery as a table almost solves problem (look at answers) - the only question now is how to select oldest rows from product_storage for multiple foreign keys (one and only one for each idproduct).

Update 2

Thanks to cmptrgeekken for great solution :))

Answer 1

this works almost fine:

SELECT p.idproduct, p.name, p.tax,
       sub.price, (sub.price * (1+tax/100)) as price_with_tax
FROM product p,
    (SELECT s.idproduct, s.price
     FROM product_storage s
     WHERE quantity > 0
     ORDER BY added ASC) sub
WHERE p.idproduct=sub.idproduct

but all rows from product_storage are returned for each product :/

Answer 2

As far as MySQL is concerned, that last occurrence of the word "price" is referring to a field in Product p, hence the error. You need to refer again to the aliased subquery right above that:

SELECT p.idproduct, p.name, p.tax,
       (SELECT s.price
        FROM product_storage s
        WHERE s.idproduct=p.idproduct AND s.quantity > 0
        ORDER BY s.added ASC
        LIMIT 1) AS price,
        (       (SELECT s.price
        FROM product_storage s
        WHERE s.idproduct=p.idproduct AND s.quantity > 0
        ORDER BY s.added ASC
        LIMIT 1) * (1 + tax/100)) AS price_with_tax
FROM product p;

Answer 3

This might work better using the subquery as a table:

SELECT p.idproduct, p.name, p.tax, s.price, (s.price * (1 + p.tax/100)) as price_with_tax
FROM product p
INNER JOIN (SELECT idproduct, price
        FROM product_storage
        WHERE quantity > 0) s
ON p.idproduct = s.idproduct
INNER JOIN (SELECT idproduct, MIN(added) min
        FROM product_storage
        GROUP BY idproduct) f
ON s.idproduct = f.idproduct AND s.added = f.min

Edit: Updated because tax is in the other table, so I had to move that calculation's location.

Edit 2: OK, changed things around again to try to filter the product_storage table properly.

Answer 4

The reason you're getting the price doesn't exist error is because you can't reference aliased columns in the column list. To fix this, store the price value in a user-defined variable and reference that, like so:

SELECT p.idproduct, p.name, p.tax,
       @price := (SELECT s.price
        FROM product_storage s
        WHERE s.idproduct=p.idproduct AND s.quantity > 0
        ORDER BY s.added ASC
        LIMIT 1) AS price,
        (@price * (1 + tax/100)) AS price_with_tax
FROM product p;

Answer 5

First, you want to get the lowest storage id for the product(s).

SELECT idproduct, MIN(idstorage) idstorage FROM product_storage 
WHERE quantity>0 AND idproduct IN (#, #, ...)
GROUP BY idstorage

idstorage, idproduct, added, quantity, price Then you can join on that query and the product_storage table (again), using the min storage ID, to get your product info with the correct pricing info.

SELECT idproduct, name, description, tax, ps.price, ps.quantity,
FROM product AS p
JOIN 
(SELECT idproduct, MIN(idstorage) idstorage FROM product_storage 
WHERE quantity>0 AND idproduct IN (#, #, ...)
GROUP BY idstorage) AS min_id
ON p.idproduct=min_id.idproduct
RIGHT JOIN product_storage AS ps ON ps.idstorage=min_id.idstorage
WHERE idproduct IN (#, #, ...)