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 ?
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 
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 
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)) ; 
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; 
