0

its my first question, please bear me. I have following tables

Item

id
name
description

itemPurchaseDetail

id
item_id(FK)
branch_id(FK)
purchaseRate
date

Stock

id
item_id(FK)
branch_id(FK)
quantity

I have to get all the items in specific branch(let suppose branch_id=1), its quantity in stock, its name, its description and its last purchase rates. This bold part is smashing my head.

LINK to fiddle: fiddle

  • Isn't a Date or any order field in table itemPurchaseDetail? How do you know it is the last purchase? – McNets Feb 11 '18 at 14:48
  • last entry against each item. but date is also present for every record. you can use that too. – vicky Feb 11 '18 at 14:52
  • Please use this fiddle to define your table schema and insert some values, then add the [new] link to the question.dbfiddle.uk/… – McNets Feb 11 '18 at 14:55
  • @McNets i have updated the post with fiddle link. i haven't tried fiddle before so please be lenient. – vicky Feb 11 '18 at 16:17
  • I would also suggest that you update your question (and the fiddle) with the query(s) you've tried so far. – markp Feb 11 '18 at 16:40
0
select   i.id, 
         i.name, 
         i.description, 
         s.quantity stock,
         (select purchaseRate
          from itemPurchaseDetail
          where item_id = i.id
          order by PurchaseDate desc
          limit 1) lastPurchaseRate 
from     item i
join     (select distinct item_id, branch_id
          from itemPurchaseDetail) pd
on       pd.item_id = i.id
join     stock s
on       s.item_id = i.id
and      s.branch_id = pd.branch_id;
id | name  | description | stock | lastPurchaseRate
-: | :---- | :---------- | ----: | ---------------:
 1 | item1 | desc 1      |   100 |              222
 2 | item2 | desc 2      |   200 |              444
 3 | item3 | desc 3      |   300 |              666

dbfiddle here

  • Thats exactly what i was looking for. – vicky Feb 12 '18 at 6:31
  • I'm glad to help – McNets Feb 12 '18 at 7:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.