Selecting Lowest Entries From Multiple After Join

[Inersha]

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?

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

[Inersha]

Okay, I've got a working solution:

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.

[Old Pedant]

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

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.

[Inersha]

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).

[Old Pedant]

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.