CODEWITHSUNDEEP
mysqlsql
Ryan
Ryan

Reputation: 989

MySQL to calculate SUM with foreign key relation

I have two tables 1.material_line_item and 2.item_master there is a foreign key relation between two tables (item_master_id present in material_line_item) and there is a column called item_code in item_master. So I want a Join Query to display the item_code with my current query.

**item_master_table**

**material_line_item_table**

Upvotes: 2

Views: 2110

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521467

I would approach this by joining the item_master table to a subquery of material_line_item which calculates the aggregates you want for each item master id value. I am selecting all columns available though you are free to choose whichever columns you want.

SELECT t1.*, t2.*
FROM item_master t1
INNER JOIN
(
    SELECT item_master_id,
           SUM(received_quantity) AS Total_Received_Qty,
           SUM(ordered_quantity)  AS Total_Ordered_Qty
    FROM material_line_item
    GROUP BY item_master_id
) t2
    ON t1.id = t2.item_master_id

Upvotes: 3

Suraj
Suraj

Reputation: 3137

Try this,

select a.item_master_id,a.Total_received_qty,a.Total_ordered_Qty,b.item_code from (select      item_master_id,
        sum(received_quantity) Total_received_qty,
        sum(ordered_quantity) Total_ordered_Qty
       from material_line_item
       group by item_master_id) a, item_master b where a.item_master_id =b.item_master_id

Hope it will help.

Upvotes: 1

ZeusNet
ZeusNet

Reputation: 720

you can simply join the two tables like 

select item.item_master_id, master.item_id, Sum(received_quantity), 
sum(ordered_quantity) from material_line_item item
left join item_master master on item.item_master_id = master.id
group by item.item_master_id, master.item_id

Upvotes: 1

Related Questions