Functuin to get IN ALL - 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

SQL Server Development (2000)

Functuin to get IN ALL

Author

Topic

kprasadreddy
Starting Member

41 Posts

Posted - 2004-08-18 : 21:09:53

I am trying to query to get if a condition excists where IN ALL

Is there a function that can get where xxxx in all

Thanks,

Prasad

kprasadreddy
Starting Member

41 Posts

Posted - 2004-08-18 : 21:29:59

Say I have 2 tables Scores and Crs table.

How do I get the following results

Select count(*) from Scores where CRS_nb IN ALL(2,3,4,5,6,7)

All I am looking to get count of studdents from scores table where the students completed all the courses given ...

Thanks,

Prasad

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-18 : 21:55:12

if you have a table of the course you want you can use a join:


Select count(*) 
From
	(
	Select StudentId, CourseCnt = count(Distinct A.courseId) 
	From scores as A
	Inner Join requiredCourses as B
	On A.courseId = B.courseId
	Group By StudentId
	Having courseCnt = (Select count(*) From requiredCourses)
	) as Z

Corey

kprasadreddy
Starting Member

41 Posts

Posted - 2004-08-18 : 22:07:43

I appreciate your help but this is not what I am looking for...

Say I have Scores table with Stdnt_nb and crs_nb in Scores table.

How do I get the count of students who completed all the courses

Say

Count(stdnt_nb) wher crc_nb in all (1,2,3,4,5,6,7)

Thanks,

Prasad

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-18 : 22:27:05

My other code is an example of how to do it, provide some example data and the expect results and you'll probably get better help.

Select Count(stdnt_nb)
From scores
where crc_nb = 1
and crc_nb = 2
and crc_nb = 3
and crc_nb = 4
and crc_nb = 5
and crc_nb = 6
and crc_nb = 7

Corey

kprasadreddy
Starting Member

41 Posts

Posted - 2004-08-18 : 22:34:15

I do not want to hard code the course number Instead I need to query the course tabel to get the list of courses based on another condition.

Thanks,

Prasad

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-18 : 23:12:46

Then I think you should study my first response. The requiredCourses would be the course list you generate. The sub query gives a list of students that have completed all of the courses required and the outer select counts those students...

Corey

kprasadreddy
Starting Member

41 Posts

Posted - 2004-08-18 : 23:53:45

I am a bit confused...

I have a scrore table with STDNT_NB,CRS_NB and course table with CRS_NB and CRRCLM

I want to select count(STDNT_NB) from Score table where CRS_NB in all (Select CRS_NB from scores where CRRCLM =2)

Can anyone help

Thanks,

Prasad

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-08-19 : 00:33:44

This should do it:
[CODE]
SELECT COUNT(a.STDNT_NB)
FROM (SELECT s.STDNT_NB, COUNT(DISTINCT c.CRS_NB) as CT
FROM Course c
JOIN Score s
ON s.CRS_NB = c.CRS_NB
WHERE c.CRRCLM = 2
GROUP BY s.STDNT_NB) a
JOIN (SELECT COUNT(DISTINCT CRS_NB) as CT
FROM Course
WHERE CRRCLM = 2) b
ON a.CT = b.CT
[/CODE]

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources