how to write mysql query for the following problem?

by

The question:

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

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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.


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 Comment