ranjan satpathy ranjan satpathy - 4 months ago 8
MySQL Question

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**

Answer

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
Comments