getting max data in multiple join

i have the data like this

purchase_order (po)

po_id   pi_id    store
112     789      ABC
113     101      DEF
114     102      GHI
115     103      JKL

purchase_items (pi)

pi_id  barcode   price    date
789    123       500      2020-06-04
101    123       400      2020-06-03
102    456       500      2020-06-02
103    456       400      2020-06-01

product

barcode   product
123       milk
456       tea

I want to get the latest price for each barcode if the store exclude "GHI"

expected result

barcode  price
123      500
456      400

how should i do ?

4 answers

  • answered 2020-07-14 16:45 GMB

    One option uses a correlated subquery:

    select
        p.barcode,
        (
            select pi.price
            from purchase_items pi
            inner join purchase_orders po on po.pi_id = pi.pi_id
            where pi.barcode = p.barcode and po.store <> 'GHI'
            order by pi.date desc limit 1
        ) price
    from product p
    

  • answered 2020-07-14 16:46 slaakso

    Use join with max-function:

    select pi.barcode, max(pi.price)
    from purchase_items pi
      join purchase_order po on po.pi_id=pi.pi_id
    where po.store!='GHI'
    group by pi.barcode
    

  • answered 2020-07-14 17:12 Liki Crus

    A way of using subqueries.

    SELECT 
        DISTINCT
        a.`barcode`,
        a.`price` 
    FROM
        purchase_items a 
    WHERE a.`date` = 
        (SELECT 
            MAX(`date`) 
        FROM
            purchase_items i 
        WHERE i.barcode = a.barcode 
            AND 'GHI' != 
            (SELECT 
                store 
            FROM
                purchase_order 
            WHERE pi_id = i.`pi_id` 
            LIMIT 1)) ;
    

  • answered 2020-07-14 19:12 Gordon Linoff

    row_number() is one method:

    select pi.barcode, pi.price
    from (select pi.*, row_number() over (partition by pi.barcode order by pi.date desc) as seqnum
          from purchase_items pi
          where not exists (select 1
                            from purchase_orders po 
                            where po.pi_id = pi.pi_id and po.store = 'GHI'
                           )
         ) pi
    where seqnum = 1;