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
 Extra rows returned

Author  Topic 

sarorelasoul
Starting Member

29 Posts

Posted - 2009-04-23 : 11:49:32
Hi,

I have a query with a left outer join, I expect it to return 10 rows 1 for each academic year, the query returns null records for years that do not have matches and that's fine but the problem is when it has matches it returns 2 records 1 with the correct numbers and another null one.can any one tell me how can I remove the duplicate red ones highlighted in red

Query:
select a.group_cd, a.acad_year, a.citzn_cd, a.citzn_desc,b.sex_cd, isnull(sum(b.adm_count), 0) as total_apply,
sum(case when b.adm_appl_stat in ('Admit', 'Accept', 'Enroll') then b.adm_count else 0 end) as total_admit,
sum(case when b.adm_appl_stat in ('Accept', 'Enroll') then b.adm_count else 0 end) as total_accept,
sum(case when b.adm_appl_stat in ('Enroll') then b.adm_count else 0 end) as total_enroll
from (
select x.program_cd, x.campus_cd as group_cd, y.acad_year, z.citzn_cd, z.citzn_desc
from Program_CD x cross join Term_CD y cross join Citzn_CD z
where x.campus_cd = 'UIUC'
and z.citzn_cd='UN'
) a
left join Admission b on (a.program_cd = b.program_cd and a.acad_year = b.acad_year and a.citzn_cd = b.citzn_cd and sex_cd = 'F' )
group by a.group_cd, a.acad_year, a.citzn_cd, a.citzn_desc,b.sex_cd
order by a.acad_year


results:
UIUC 1999-2000 UN Unknown NULL 0 0 0 0
UIUC 2000-2001 UN Unknown M 5 0 0 0
UIUC 2000-2001 UN Unknown NULL 0 0 0 0
UIUC 2001-2002 UN Unknown M 2 0 0 0
UIUC 2001-2002 UN Unknown NULL 0 0 0 0
UIUC 2002-2003 UN Unknown NULL 0 0 0 0
UIUC 2002-2003 UN Unknown M 1 1 1 1
UIUC 2003-2004 UN Unknown NULL 0 0 0 0
UIUC 2004-2005 UN Unknown M 5 1 0 0
UIUC 2004-2005 UN Unknown NULL 0 0 0 0
UIUC 2005-2006 UN Unknown NULL 0 0 0 0
UIUC 2005-2006 UN Unknown M 9 2 2 2
UIUC 2006-2007 UN Unknown NULL 0 0 0 0
UIUC 2007-2008 UN Unknown NULL 0 0 0 0
UIUC 2008-2009 UN Unknown NULL 0 0 0 0

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-23 : 12:06:40
to avoid rows for non matching joines add a WHERE b.program_cd is not null
or do an inner join.

Webfred


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

sarorelasoul
Starting Member

29 Posts

Posted - 2009-04-23 : 14:44:40
I tried both, but they remove all the null rows and I don't want to remove them all, only the duplicate ones so that I have one record per year.
Go to Top of Page

Qualis
Posting Yak Master

145 Posts

Posted - 2009-04-23 : 15:49:02
Are you sure that the query you posted gives the results you posted? I'm pretty sure it doesn't because you are restricting b.sex_cd = 'F'. With that restriction, you would never get the M records you posted. Can you confirm your query for us?
Go to Top of Page

sarorelasoul
Starting Member

29 Posts

Posted - 2009-04-23 : 15:55:56
OOPs the sex_cd='M'
Here is the correct query:
select a.group_cd, a.acad_year, a.citzn_cd, a.citzn_desc,b.sex_cd, isnull(sum(b.adm_count), 0) as total_apply,
sum(case when b.adm_appl_stat in ('Admit', 'Accept', 'Enroll') then b.adm_count else 0 end) as total_admit,
sum(case when b.adm_appl_stat in ('Accept', 'Enroll') then b.adm_count else 0 end) as total_accept,
sum(case when b.adm_appl_stat in ('Enroll') then b.adm_count else 0 end) as total_enroll
from (
select x.program_cd, x.campus_cd as group_cd, y.acad_year, z.citzn_cd, z.citzn_desc
from Program_CD x cross join Term_CD y cross join Citzn_CD z
where x.campus_cd = 'UIUC'
and z.citzn_cd='UN'
) a
left join Admission b on (a.program_cd = b.program_cd and a.acad_year = b.acad_year and a.citzn_cd = b.citzn_cd and sex_cd = 'M' )
group by a.group_cd, a.acad_year, a.citzn_cd, a.citzn_desc,b.sex_cd
order by a.acad_year

Go to Top of Page

Qualis
Posting Yak Master

145 Posts

Posted - 2009-04-23 : 16:33:17
Well, it looks like you only want Males in this query, so try changing your select and group by of b.sex_cd to IsNull(b.sex_cd, 'M'). This will eliminate the extras.
Go to Top of Page
   

- Advertisement -