JOining Three Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

JOining Three Tables

Author

Topic

karrojo
Starting Member

26 Posts

Posted - 2011-06-08 : 06:17:18

Good Day!

I am just new in using JOIN in matching records and get confused on the result. i have a required output but im not getting the result that i wanted.

i have three tables: information, check1, and check2 with claim_no as the common key.

my expected output is that in one column i will get the sum of all checks issued to table Check1 and another column the sum of checks to table Check2. i am getting the results, however, the sum of column for table Check1 and the sum of column for table check2 is multiple times over the expected sum.

theoretical sample:
information table
claimno, lname, fname, mi
1 smith john c
2 doe john q

check1 table
claimno, checkno, date_issue, amount
1 10 01/01/2011 500
1 20 02/01/2011 1000
2 10 01/01/2011 500
2 20 02/01/2011 1000

check2 table
claimno, checkno, date_issue, amount
1 30 01/20/2011 500
1 40 02/20/2011 1000
2 30 01/20/2011 500
2 40 02/20/2011 1000

query:
select claimno, lname, fname, mi, sum(b.amount) as c1, sum(c.amount) as c2
from information a inner join check1 b on a.claimno = b.claimno
left outer join check2 c on a.claimno = c.claimno

Result:
claimno lname fname mi c1 c2
1 smith john s 25000 25000
2 doe john q 50000 50000

my error lies on the sum on column c1 and c2, it is multiple times over. i really need all your help.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-08 : 06:37:31

[code]
select a.claimno, a.lname, a.fname, mi, b.amount as c1, c.amount as c2
from information a
join (select claimno, amount = sum(amount) from check1 group by claimno) b
on a.claimno = b.claimno
left join (select claimno, amount = sum(amount) from check2 group by claimno) c
on a.claimno = c.claimno
[/code]

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

karrojo
Starting Member

26 Posts

Posted - 2011-06-08 : 20:19:49

thanks so much nigel :)

karrojo
Starting Member

26 Posts

Posted - 2011-06-08 : 21:10:45

i have an additional question Nigel,

If i add another table, will i used the same join condition in the from clause?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-09 : 08:15:19

If claimno is the linking value then yes.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

karrojo
Starting Member

26 Posts

Posted - 2011-06-10 : 05:29:10

thanks so much for your help Nigel :D I finished my report.

More power!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources