#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep Power
    0

    Help needed for nested SELECTs


    Hi there

    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,

    Jon
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,575
    Rep Power
    1906
    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)
    Code:
    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
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep Power
    0
    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.

    Thanks again.

    Jon


    Originally Posted by MrFujin
    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)
    Code:
    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

IMN logo majestic logo threadwatch logo seochat tools logo