2

I am working on a ecommerce project and want to select suppliers based on oldest date...Here is my DB design

Product

id(PK) name            price  added_date(DATE)
1      Samsung Note    180000  2016-09-30
2      Nokia Lumia 510 215000  2016-09-29

Supplier

id(PK) name     registered_date(timestamp) stock
1  Easyshop 2016-09-30 12:50:12             10
2  T-mobile 2016-09-30 12:55:36             10

product_supplier

itemid(pk) supplierid(pk)
 1          2
 1          1

As you can see the item 1 as 2 suppliers. So, What i want to do: is to select items based on first added suppliers (like in FIFO method- first in - first out)

How can do such join query with timestamp as condition?

newdeveloper
  • 19
  • 1

3 Answers3

0

If you want only one record then below query should be work

    select product.* from product p LEFT JOIN product_supplier on product.id = itemid 
LEFT JOIN supplier ON supplierid = supplier.id 
order by registered_date ASC 
LIMIT 1;
Bhavin Solanki
  • 1,364
  • 11
  • 27
0

You can use sub query. In subquery use GROUP BY and Max.

SELECT PS.itemid, PS.supplierid, P.name, P.price, P.added_date, S.name, S.registered_date FROM product_supplier AS PS 
LEFT JOIN Product AS P ON P.id = PS.itemid
LEFT JOIN Supplier AS S ON S.id = PS.supplierid
GROUP BY PS.itemid 
ORDER BY PS.itemid LIMIT 1
Ramin Darvishov
  • 1,043
  • 1
  • 15
  • 30
0

Hope this will work for you...

select  product_supplier.*, product.name, supplier.name, from 
    product_supplier 
    join product on (product_supplier.itemid = product.id)
    join supplier on (product_supplier.supplierid = supplier.id)
    order by supplier.registered_date asc
Skalbhile
  • 156
  • 13