November 17th, 2012, 10:47 AM
Help needed for nested SELECTs
I have two tables, one containing products and one containing options for each product. The options table has an ID field to link the records to the products table.
When showing a list of products, I also want to show the cheapest price from the options table.
eg. Product 1 is paint. It has three options - white £9.99, black £10.99 and colour £14.99.
When listing the products I want it to say 'Paint - From £9.99'.
I've been trying to construct a query which has a nested SELECT statement to get the MIN value for price in the options table but I just can't get it to work.
Could someone help me with how I construct this?
Many thanks in anticipation,
November 17th, 2012, 10:52 AM
Would be great if you had posted the SQL you tried.
General, you should be able to get the information with something like this: (untested)
SELECT p.ProductName , MIN(po.Price)
FROM Products p
INNER JOIN ProductOptions po ON p.pid = po.pid
WHERE p.ProductName = "Product 1"
GROUP BY p.ProductName
November 17th, 2012, 11:02 AM
Now that's what I call a speedy response! Many thanks for that. I didn't paste my attempt because it's already a lot more complicated than just the issue I posted, but I was already going down the wrong path in trying a SELECT in the FROM part. Here's that bit:
FROM products, (SELECT MIN(product_options.poPrice) AS fromprice FROM product_options WHERE product_options.poProductID = products.ProductID) AS priceoptions
I think I got it completely wrong! I'll try your suggestion now.
Originally Posted by MrFujin