Subquery and select 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 may be a different price, and the oldest - "sell first"

For example, in my repository:

 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 the current price, which is in the oldest row in product_storage, where the quantity is> 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 that doesn’t mean when I want to calculate the price with tax in the request:

 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 a subquery as a table almost solves the problem (see answers) - the only question is how to select the oldest rows from product_storage for several foreign keys (one and only one for each idproduct).

Update 2

Thanks cmptrgeekken for a great solution :))

Answer 1

The reason you get the price doesn't exist error is because you cannot reference columns with an alias in the column list. To fix this, save the price value in a user variable and indicate that this is 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 2

This might work better using a table subquery:

 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 the tax is in a different table, so I had to move this calculation location.

Edit 2: OK, everything has changed to try to filter the product_storage table correctly.

Answer 3

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 4

As far as MySQL is concerned, the last occurrence of the word “price” refers to a field in Product p, hence the error. You need to call the subquery again with the alias located above:

 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 5

First you want to get the smallest repository identifier 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 this query and the product_storage table (again) using the minimum storage identifier to get information about your product with the correct price information.

 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 (#, #, ...)