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 |
|
joeyc0323
Starting Member
2 Posts |
 Posted - 2012-06-20 : 09:41:45
|
| I need to run a comparison on a database but it is not one I am used to writing. I have a list of school districts and schools with their grades (01, 02, 03, 04...ect). I need to verify that every district and school has all grades represented. I have three individual columns, one for district, one for school, and one for grade.Can anyone offer a solution to this? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-20 : 10:06:21
|
| select district, schoolfrom tblgroup by district, schoolhaving count(*) <> 10If there are 10 grades==========================================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. |
 |
|
|
joeyc0323
Starting Member
2 Posts |
Posted - 2012-06-20 : 10:11:39
|
Thanks! Works great! One question though, is it possible to see in the output what grades the school may be missing?quote:
Originally posted by nigelrivett
select district, schoolfrom tblgroup by district, schoolhaving count(*) <> 10If there are 10 grades==========================================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.
|
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-20 : 10:33:39
|
| select t2.district, t2.school, t1.gradefrom (select distinct grade from tbl) t1cross join (select distinct district, school frrom tbl) t2left join tbl t3on t2.district = t3.districtand t2.school = t3.schooland t1.grade = t3.gradewhere t3.district is nullorder by t2.district, t2.school, t1.grade==========================================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. |
|
|
|
|
|
|
|
|
Comparison