how write mysql query for the following stock inventory tables?

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

Answer 1

You need to SELECT from the product table first for including All products and then join other tables to get extra informations from them.

Try this :

SELECT a.item_cid, a.item_id, a.item_name, b.current_stock, c.purchase_qty, b.current_stock + c.purchase_qty AS 'Total'
FROM table1 AS a
LEFT JOIN table2 AS b ON a.item_id=b.item_id
LEFT JOIN table3 AS c ON a.item_id=c.item_id
ORDER BY a.item_id