Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Jun 2008
    Posts
    50
    Thanks
    18
    Thanked 0 Times in 0 Posts

    Selecting Lowest Entries From Multiple After Join

    I have two tables:

    1) Products - This has a list of different products.

    1 - TV
    2 - Radio
    3 - Book

    2) Retailers - This lists the retailer's prices for these products.

    Amazon - TV - $100
    Amazon - Radio - $60
    Amazon - Book - $10
    Play - TV - $90
    Play - Radio - $50
    Amazon - Book - $20
    Tesco - TV - $120
    Tesco - Radio - $30
    Tesco - Book - $15


    So after joining these two tables on the product name, I end up with multiple product listings like this:

    1 - TV - Amazon - $100
    1 - TV - Play - $90
    1 - TV - Tesco - $120
    2 - Radio - Amazon - $60
    2 - Radio - Play - $50
    2 - Radio - Tesco - $30
    3 - Book - Amazon - $10
    3 - Book - Play - $20
    3 - Book - Tesco - $15


    How would I go about writing a mysql statement that would return a table with only the lowest price for each individual product. So something that would look like this:

    1 - TV - Play - $90
    2 - Radio - Tesco - $30
    3 - Book - Amazon - $10


    Here's a simple version of my current mysql statement. Any ideas on how best to modify it to return just one listing of each product with the lowest price and its retailer?

    Code:
    SELECT * FROM products
    INNER JOIN retailers ON products.product = retailers.product

  • #2
    New Coder
    Join Date
    Jun 2008
    Posts
    50
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Okay, I've got a working solution:

    Code:
    SELECT *, MIN(retailers.price) FROM products
    INNER JOIN retailers ON products.product = retailers.product
    GROUP BY products.product
    Not sure if it's the best solution, so I'd be happy to hear if there's anything glaringly awful about it.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,857
    Thanks
    78
    Thanked 4,417 Times in 4,382 Posts
    Well, yes, but...

    When you use GROUP BY, you really should never use SELECT *

    And you should then GROUP BY *ALL* the fields that are not aggregate functions (i.e., MIN, MAX, AVG, COUNT, etc.)

    So better would be
    Code:
    SELECT products.productid, products.product, MIN(retailers.price) AS bestPrice
    FROM products INNER JOIN retailers ON products.product = retailers.product
    GROUP BY products.productid, products.product
    just as an example.

    Databases other than MySQL will insist on this. MySQL allows you to be sloppy, but the result of the GROUP BY is then not always what you wanted it to be.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Inersha (09-08-2011)

  • #4
    New Coder
    Join Date
    Jun 2008
    Posts
    50
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Okay, thank you for pointing that out Old Pedant. I'll get on to it. Just so I know, what is the reason behind using GROUP BY on every other field? Why is that necessary?



    Also as an aside, just so I fully understand what's going on:

    When you SELECT the fields in the first line before the JOIN statement, you are selecting the fields that you anticipate will exist in the new table after the JOIN? It just seems a bit strange to have the SELECT statement come before the newly joined table (and before those new fields are added).

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,857
    Thanks
    78
    Thanked 4,417 Times in 4,382 Posts
    It's just the prescribed syntax of ANSI (standards committee) SQL. Many many many years old standard.

    The prescribed order is:
    SELECT
    FROM
    WHERE
    GROUP BY
    HAVING
    ORDER BY
    LIMIT [only LIMIT is a MySQL addition...rest are all ANSI]

    Regarding GROUP BY:

    Kind of a good question. You would think that the SQL compiler could create the GROUP BY automatically. But it doesn't. Again, probably historical. 25 or more years ago, when computers weren't so powerful, it probably made sense to put the burden of figuring out what field were used for what on the programmer. Doesn't so much seem a good idea nowadays.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •