Join 3 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

Join 3 tables

Author

Topic

sarorelasoul
Starting Member

29 Posts

Posted - 2009-07-19 : 21:12:02

Hi,

I'm joining 3 tables together and need help in writing the query.
Below are the tables and some sample data
table 1: Program
-----------------
program_cd group_cd acad_year
---------------------------------
20k 20k 1999-2000
20k 20k 2000-2001
20k 20k 2001-2002
-
-
-
20k 20k 2007-2008
20k 20k 2008-2009

table 2 : Admission
-------------------
program_cd acad_year term_cd sex_cd citzn_cd race_cd state ct
------------------------------------------------------------------
20k 1999-2000 1 F US 2 admit 1
20K 1999-2000 1 M US 3 admit 1
20K 1999-2000 1 M PR 3 enroll 1
20K 1999-2000 1 M PR 3 apply 1
20K 2001-2002 2 M PR 3 enroll 1
20K 2004-2005 1 M PR 3 apply 2 20K 2004-2005 1 M PR 3 accept 1
table 3 : Petition
-------------------
program_cd acad_year term_cd sex_cd citzn_cd race_cd p_ct
------------------------------------------------------------------
20k 1999-2000 1 F US 2 1
20K 2004-2005 1 M PR 3 1

Here is te query
select a.group_cd, a.acad_year, 'All' as type,
isnull(sum(b.ct), 0) as total_apply,
sum(case when b.state in ('Admit', 'Accept', 'Enroll') then b.ct else 0 end) as total_admit,
sum(case when b.state in ('Accept', 'Enroll') then b.ct else 0 end) as total_accept,
sum(case when b.state in ('Enroll') then b.ct else 0 end) as total_enroll,
sum(case when c.p_ct is not null then c.p_ct else 0 end ) as total_pet
from program a
left join Admission b on (a.program_cd = b.program_cd and a.acad_year = b.acad_year)
left join Petition c on ( a.program_cd= c.program_cd and a.acad_year = c.acad_year and b.term_cd = c.term_cd and b.sex_cd = c.sex_cd and b.citzn_cd = c.citzn_cd )
group by a.group_cd, a.acad_year
order by a.acad_year

The problem is that the total_pt is'nt correct, it's greater than it should be,I guess that's because of the state column in the 2nd table, the p_ct counts more than one time!!!.
can anybody help?

Thanks

sarorelasoul
Starting Member

29 Posts

Posted - 2009-07-20 : 13:00:13

Actually I found that all the sums turn out wrong in the query. when I join the 1st and 2nd table in a query and the 1st and 3rd table in a query the results are correct, but once I join the 3 together the nummbers go crazy.
Does anybody have an idea why??

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources