all conditions met? - 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

SQL Server 2000 Forums

Transact-SQL (2000)

all conditions met?

Author

Topic

dcummiskey
Starting Member

26 Posts

Posted - 2007-07-05 : 13:52:35

I'm trying to find a group of students that do not have scores for a specific test type across multiple units:


select * from tblREsultsStudentRaw where testid = 7 and unit in (6,12,18,24,30,36) and valid <> 1 and classid = 11477

Any suggestions on how I would write this so the results would bring back students who did not have a valid flag <> 1 in all of the units (6,12,18,24,30,36). Right now it returns students who have and invalid flag in any of the units.

thanks,
Dan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-05 : 14:56:30

I'm not sure I really understand your requirements; this is a bit confusing "...who did not have a valid flag <> 1 in all of the units...", but give this a try.


select
	StudentID
from
	tblREsultsStudentRaw
where
	testid = 7 and
	unit in (6,12,18,24,30,36) and
	valid <> 1 and
	classid = 11477
group by
	StudentID
having
	count(*) <> 6

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources