What is SQL Query:Master child table [modified]

[xodeblack]

I have following two tables 1st table is Fee:

PK ---- payable
1 ---- 1500
2 ---- 1200
3 ---- 1900

2nd table is Fee_Detail:

pk ---- paid ---- fk_fee
1 ---- 700 ---- 1
2 ---- 400 ---- 1
3 ---- 400 ---- 1
4 ---- 800 ---- 2
5 ---- 300 ---- 2

Result should be

payable ---- paid
1500 ---- 1500
1200 ---- 1100
1900 ---- 0

what is sql query

modified on Wednesday, September 2, 2009 2:39 AM

[Mycroft Holmes]

You need and inner join a group by and a sum

Select FeeID, sum(payable), sum(paid)
from Fee
iner join Fee_detail FD on FD.fk_fee = fee.FeeID
group by
FeeID

[xodeblack]

I want to fetch all records of left hand side and only sum of 'paid amount' matching records of child table(right hand side) according to fk_fee(foreign key column).. for example Fee table: Pk ---- payable 1 ---- 1500 2 --- 700 Fee Detail: Pk ---- Paid ---- fk_fee 1 ---- 1100 ----1 2 ---- 200 ----1 3 ---- 100 ----1 Result should be payable ---- paid 1500 ---- 1400 700 ---- 0

[Mycroft Holmes]

Then you need to use a left join and deal with null values

Select FeeID, sum(payable), sum(ISNULL(paid,0))
from Fee
left join Fee_detail FD on FD.fk_fee = fee.FeeID
group by
FeeID

search here for articles on joins, there is a good one around, sorry I don't have a link

[Nisha Agrawal]

Below is the query for your requirement. You need to group by each field that you want in the select statement.

SELECT Fee.Payable, ISNULL( SUM(Fee_Detail.Paid) ,0)
FROM Fee Left Outer Join Fee_Detail ON Fee.PK_Id = Fee_Detail.FK_Id
Group By FK_Id, Fee.Payable

Hope it helps you.