CODEWITHSUNDEEP
mysqljoinmin
Tim M
Tim M

Reputation: 99

MYSQL SELECT on two tables with MIN Value

I have two tables, one is a products table, and the other is an offers table for different vendors offering the same product.

Table1:

sku   name
----|-----
a   | Iphone
b   | Galaxy 5
c   | Nexus 6

Table2:

sku   price   vendor
----|-------|--------
a   | 5.00  | storeX
a   | 6.00  | storeY
a   | 7.00  | storeZ
b   | 15.00 | storeP
b   | 20.00 | storeQ
b   | 30.00 | storeR
c   | 11.00 | storeD
c   | 12.00 | storeF
c   | 13.00 | storeG

I am trying to run a SELECT on these tables so I can get the lowest offer for each item. So my result would be:

sku   price    vendor
----|--------|--------
a   | 5.00   | storeX
b   | 15.00  | storeP
c   | 11.00  | stored

I have tried SELECT table1.sku,table2.price FROM table2 JOIN table1 ON table2.sku = table1.sku WHERE table2.sku IN ('a','b','c'); But that just give me all offers. Any help with this query is appreciated.

Upvotes: 1

Views: 577

Answers (1)

Marc B
Marc B

Reputation: 360702

You need two queries: one to determine the min price per product, and then a parent query to select the other fields related to that min price:

SELECT table2.*, table1.name
FROM table2
LEFT JOIN  (
   SELECT MIN(price) AS min, sku
   FROM table2
   GROUP BY sku
) AS child ON ((table2.price = child.min) AND (table2.sku = child.sku))
LEFT JOIN table1 ON (table2.sku = table1.sku)

This also has the advantage of showing if multiple stores have the same minimum price.

Upvotes: 1

Related Questions