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.
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 redQuery: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 zwhere 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_yearresults:UIUC 1999-2000 UN Unknown NULL 0 0 0 0UIUC 2000-2001 UN Unknown M 5 0 0 0UIUC 2000-2001 UN Unknown NULL 0 0 0 0UIUC 2001-2002 UN Unknown M 2 0 0 0UIUC 2001-2002 UN Unknown NULL 0 0 0 0UIUC 2002-2003 UN Unknown NULL 0 0 0 0UIUC 2002-2003 UN Unknown M 1 1 1 1UIUC 2003-2004 UN Unknown NULL 0 0 0 0UIUC 2004-2005 UN Unknown M 5 1 0 0UIUC 2004-2005 UN Unknown NULL 0 0 0 0UIUC 2005-2006 UN Unknown NULL 0 0 0 0UIUC 2005-2006 UN Unknown M 9 2 2 2UIUC 2006-2007 UN Unknown NULL 0 0 0 0UIUC 2007-2008 UN Unknown NULL 0 0 0 0UIUC 2008-2009 UN Unknown NULL 0 0 0 0Thanks |
|
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 nullor do an inner join.Webfred No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
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. |
|
|
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? |
|
|
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_enrollfrom (select x.program_cd, x.campus_cd as group_cd, y.acad_year, z.citzn_cd, z.citzn_descfrom Program_CD x cross join Term_CD y cross join Citzn_CD zwhere x.campus_cd = 'UIUC'and z.citzn_cd='UN') aleft 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_cdorder by a.acad_year |
|
|
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. |
|
|
|
|
|
|
|