SQL query in Mysql

Question

I am using php and Mysql and need help to display UNIQUE results for a price comparison.

My tables are:

tblItems

ID | SKU | Shop | Name | Size
1     S1   Shop1   A      S
2     S2   Shop1   B      M
3     S3   Shop1   C      L
4     S4   Shop2   A      S

tblProductFeed

ID | SKU | Shop | Price
1     S1   Shop1   12
2     S2   Shop1   14
3     S3   Shop1   15
4     S4   Shop2   11

The idea is a price comparison.

I want to compare all records where Size='S'. But I only want to get the lowest price from tblProductFeed, and I only want one record where the price is lowest.

My thoughts are like:

Get SKU, Shop and Name from tblItems where Size='S'
Compare matching rows in tblProductFeed where SKU and Shop are matching
Get the lowest price from tblProductFeed

Display only one DISTINCT Name from TblItems and with the lowest price

Results:

Name | Price
A       11
B       14
C       15

Maybe my idea is bad, please correct me.

Before I decided to scrap data and build to tables my sql was like this, might help to get some inspiration.

    SELECT tbl.* 
      FROM tblProductFeed tbl 
INNER JOIN (SELECT Size
                   , MIN(Price) MinPrice 
              FROM tblProductFeed 
             WHERE Size = '". $str ."' 
             GROUP BY Size) tbl1 
        ON tbl1.Size = tbl.Size 
     WHERE tbl1.MinPrice = tbl.Price;

Dody Suhada · Accepted Answer · 2017-08-11 09:43:08Z

1

try this : to retrieve lowest price for data with size 'S'

select a.Name, a.Shop, b.Price as minprice from tblItems as a
inner join tblProductFeed as b on 
         a.id = b.id and
         a.SKU = b.SKU and
         a.Shop = b.shop
where a.size = 'S' and b.price in (select min(price) from tblProductFeed)

Result :

Or if you want to get all item with lowest price try this :

select name,min(price)minprice from 
(select a.id, a.SKU, b.shop, a.name, a.size, b.Price from tblItems as a
    inner join tblProductFeed as b on 
        a.id = b.id and
        a.SKU = b.SKU and
        a.Shop = b.shop
where b.price in (select min(price) from tblProductFeed group by price)) a
group by name

Result :

edited Aug 11, 2017 at 9:43

answered Aug 11, 2017 at 9:06

Dody Suhada

1181 gold badge1 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

devojuhyg Over a year ago

Thank you very much, I will test the code asap and get back.

Collectives™ on Stack Overflow

SQL query in Mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related