phpmyadmin Question posted in
The official documentation can be found here.

Multi Join Select – Phpmyadmin

Let’s take table with name Items and columns: Item,Seller,Price,Date.
Now I need to such selector: for any item we need to select minimum Price in some Date interval which was suggested by Seller,if there is more than 1 Date,give only Later) and if on that date are more than 1 seller,show all.
I wrote some code,but It does not take minimum date from given list.
What I need to add in selector to get result that I need?

SELECT *    
FROM Items mt 
    INNER JOIN
    (
        SELECT Item, MIN(Price) MinPrice
        FROM Items    
        GROUP BY Item
    ) 
    t ON mt.Item = t.Item AND mt.Price = t.MinPrice
    where Date > '2019-04-01' and Date < '2019-04-15'
    sample: 
    ItemA SellerA 10 2019-04-02
    ItemA SellerB 10 2019-04-03
    ItemA SellerC 10 2019-04-07
    ItemA SellerD 20 2019-04-05
    ItemA SellerE 10 2019-04-06
    ItemA SellerF 10 2019-04-02
    ItemA SellerG 20 2019-04-07

    result:
    ItemA SellerC 10 2019-04-07
    ItemA SellerG 20 2019-04-07
Tags:

2

Answers


  1. 0

    I think you want:

    SELECT it.*    
FROM Items it INNER JOIN
     (SELECT Item, MIN(Price) as MinPrice
      FROM Items  
      WHERE Date > '2019-04-01' AND Date < '2019-04-15'  
      GROUP BY Item
     ) itmin
     ON it.Item = itmin.Item AND it.Price = itmin.MinPrice
WHERE it.Date > '2019-04-01' AND it.Date < '2019-04-15' ;
    Login or Signup to reply.
  2. 0

    So we can’t join on date as grouping by it may not give us a min price within a date range. So we have to use two where clauses.

    So we use inner query Z to get us the min price and max date per item within the date range required. Then join back to your base set to get all sellers which have that max date.

    Untested:

    SELECT I.Item, I.Seller, I.Price,I.Date
FROM ITEMS I
INNER JOIN (SELECT min(price) MinPrice, max(date) maxDate, item
            FROM items 
            WHERE Date > '2019-04-01' and < '2018-04-15'
            GROUP BY Item) Z
  ON I.Item = Z.Item
 AND I.Price =  Z.Price
 and I.Date = Z.MaxDate
WHERE I.Date > '2019-04-01' and < '2018-04-15'

    But I don’t think this is right yet… as max date may not be the date associated to the min price…

    So…

    SELECT I.Item, I.Seller, I.Price,I.Date
FROM ITEMS I
INNER JOIN (SELECT min(price) MinPrice, item
            FROM items 
            WHERE Date > '2019-04-01' and < '2018-04-15'
            GROUP BY Item) Z
  ON I.Item = Z.Item
 AND I.Price =  Z.Price
 INNER JOIN (SELECT max(Date) MaxDate, item, Price
            FROM items 
            WHERE Date > '2019-04-01' and < '2018-04-15'
            GROUP BY Item, Price) Y
  ON I.Item = Y.Item
 AND I.Price =  Y.Price
 AND I.Date = Y.MaxDate
WHERE I.Date > '2019-04-01' and < '2018-04-15'

    But then I think there should be a way to to simplify this so i’m not having to have 3 where clauses…

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search