Problem with WHERE IN

[Tokci]

I've 2 tables students and exams.
Now I've to count the no of students who have appeared for 'n' no of exams.
I used Where in clause but it works more like an 'OR'.

Also the list of exams is passed through a parameter and the no is not fixed.
How do I check for students that have appeared in all the exams in the list.
I hope I've made the question clear.

TIA

[willakawill]

I've 2 tables students and exams.
Now I've to count the no of students who have appeared for 'n' no of exams.
I used Where in clause but it works more like an 'OR'.

Also the list of exams is passed through a parameter and the no is not fixed.
How do I check for students that have appeared in all the exams in the list.
I hope I've made the question clear.

TIA

Hi. Not too difficult if you post the table structures please.

[Tokci]

Hi. Not too difficult if you post the table structures please.

Sorry sould have done that earlier...actually there are 3 tables :

Students
 -StudentID  (PK)
 -Student Name
 
Exams
 -ExamID  (PK)
 -Description
 
Details
 -DetailID (Identity)
 -ExamID  (FK)
 -StudentID  (FK)

I need no of students who have appeared in the chosed exams.

[willakawill]

Sorry sould have done that earlier...actually there are 3 tables :

Students
 -StudentID  (PK)
 -Student Name
 
Exams
 -ExamID  (PK)
 -Description
 
Details
 -DetailID (Identity)
 -ExamID  (FK)
 -StudentID  (FK)

I need no of students who have appeared in the chosed exams.

OK Try this out

SELECT COUNT([StudentID])
FROM Details D
INNER JOIN Exams E
ON D.ExamID = E.ExamID
WHERE E.Description = 'put your description here'
 

[Tokci]

OK Try this out

SELECT COUNT([StudentID])
FROM Details D
INNER JOIN Exams E
ON D.ExamID = E.ExamID
WHERE E.Description = 'put your description here'
 

This won't help 'coz I need the no of students appeared in ALL the chosen exams.
ie. if I select English Hindi and Maths....I must get the total no of students that appeared in all the 3 exams.

If I use "E.descrition = " , I can only specify one description and if I use "E.Description in" the result I get is not what I want...it returns the no of students who have given 1 or more than 1 of the exams selected.

I hope this time I'm able to clearly state the problem.

[willakawill]

This won't help 'coz I need the no of students appeared in ALL the chosen exams.
ie. if I select English Hindi and Maths....I must get the total no of students that appeared in all the 3 exams.

If I use "E.descrition = " , I can only specify one description and if I use "E.Description in" the result I get is not what I want...it returns the no of students who have given 1 or more than 1 of the exams selected.

I hope this time I'm able to clearly state the problem.

OK try this:

SELECT Exams.Description, COUNT([Details].[StudentID])
FROM Details D
INNER JOIN Exams E
ON D.ExamID = E.ExamID
GROUP BY E.Description
 

[Tokci]

OK try this:

SELECT Exams.Description, COUNT([Details].[StudentID])
FROM Details D
INNER JOIN Exams E
ON D.ExamID = E.ExamID
GROUP BY E.Description
 

This will simply give the no of students for EACH exam.
Will give u another example :
Suppose there are 3 exams - x1, x2, x3
we have 5 students - a,b,c,d,e

Now,
a,b,c,d,e have given x1
b,c,d have given x2
a,d,e have given x3

that means if I want to know the no of students who have given all the exams then count should be 1 (Only B has given all the 3 exams). Similarily if I choose x1,x3 the it should return 3 (a,d,e).

Where as with our current query it will simply return :
x1 - 5
x2 - 3
x3 - 3

(I hope I'm interpretting ur query right).

BTW I really appreciate ur prompt replies and help.

[willakawill]

Sorry sould have done that earlier...actually there are 3 tables :

Students
 -StudentID  (PK)
 -Student Name
 
Exams
 -ExamID  (PK)
 -Description
 
Details
 -DetailID (Identity)
 -ExamID  (FK)
 -StudentID  (FK)

I need no of students who have appeared in the chosed exams.

Give this one a run around the block :)

SELECT COUNT([StudentID]}
FROM Details D
INNER JOIN Exams E
ON D.ExamID = E.ExamID
WHERE StudentID IN (
SELECT StudentID
FROM Details D
INNER JOIN Exams E
ON D.ExamID = E.ExamID
WHERE StudentID IN (
SELECT StudentID
FROM Details D
INNER JOIN Exams E
ON D.ExamID = E.ExamID
WHERE E.Description = 'Maths'
)
AND E.Description = 'English'
)
AND E.Description = 'SQL Programming'