how to write mysql query for the following problem?

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply