1

products table table1 which has item names

item_cid item_id item_name
8 403 Tequila1
8 404 Tequila2
8 405 Tequila3
8 406 Tequila4
8 407 Tequila5
8 408 Tequila6
2 409 budwiser1
2 5 budwiser2
2 7 budwiser4
2 8 budwiser5

table2 which has a current stock

item_cid item_id item_name current stock
8 403 Tequila1 11
8 404 Tequila2 10
8 405 Tequila3 32
8 406 Tequila4 44
2 409 budwiser1 55
2 5 budwiser2 58

table3 which has purchase stock

item_cid item_id item_name purchase qty
8 407 Tequila5 4
8 408 Tequila6 7
2 7 budwiser4 8
2 8 budwiser5 9
2 409 budwiser1 5
2 5 budwiser2 2

the result I want

item_cid item_id item_name current stock purchase qty total
8 403 Tequila1 11 null 11
8 404 Tequila2 10 null 10
8 405 Tequila3 32 null 32
8 406 Tequila4 44 null 44
8 407 Tequila5 null 4 4
8 408 Tequila6 null 7 7
2 409 budwiser1 55 5 60
2 5 budwiser2 58 2 60
2 7 budwiser4 null 8 8
2 8 budwiser5 null 9 9

the query I tried is not able to retrieve rows from table 3 in the final result this what I am getting

item_cid item_id item_name current stock purchase qty total
8 403 Tequila1 11 null 11
8 404 Tequila2 10 null 10
8 405 Tequila3 32 null 32
8 406 Tequila4 44 null 44
8 407 Tequila5 null 4 4
8 408 Tequila6 null 7 7
2 409 budwiser1 55 5 60
2 5 budwiser2 58 2 60
SELECT 
   *

FROM
   table2 a
       LEFT JOIN
   table3 b ON a.item_id = b.item_id
        JOIN
   table1 d ON  d.item_id=a.item_id
   ORDER BY a.item_id
2
  • hey guys any answers for this will appreciated
    – gopal
    Mar 11, 2022 at 6:54
  • 1
    1 hour is too fast to expect an answer.
    – Rick James
    Mar 11, 2022 at 20:52

1 Answer 1

1
SELECT item_cid, 
       item_id, 
       item_name,
       SUM(table2.current_stock) current_stock,
       SUM(table3.purchase_qty) purchase_qty,
       COALESCE(SUM(table2.current_stock), 0) + COALESCE(SUM(table3.purchase_qty), 0) total
FROM table1
LEFT JOIN table2 USING (item_cid, item_id, item_name)
LEFT JOIN table3 USING (item_cid, item_id, item_name)
GROUP BY item_cid, item_id, item_name

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=aeac4b2165c8b8370fbbc082f08b3145

PS. Your tables are not normalized.

2
  • thanks for the answer so working great but small change this is date wise entry to table2 and the current stock value is the previous date's current stock value and that is added to the current date purchase value
    – gopal
    Mar 11, 2022 at 10:41
  • 1
    "Auntie, give me some water to drink because I want to eat so much that I have nowhere to spend the night." © Another problem - another question.
    – Akina
    Mar 11, 2022 at 11:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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