Query Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everybody,

I have a table storing the test dates, student names and the grade of
the students. I am now going to find out those dates with student1 got
A and student2 got B and vice versa (i.e. student1 got B and student2
got A). How can I do that? I think it is quite complicate for me.
Please help.
I'm using SQL server 2000

FRANKLIN
 
Hi everybody,

I have a table storing the test dates, student names and the grade of
the students. I am now going to find out those dates with student1 got
A and student2 got B and vice versa (i.e. student1 got B and student2
got A). How can I do that? I think it is quite complicate for me.
Please help.
I'm using SQL server 2000

FRANKLIN
Click to expand...

You may want to post in a SQL/Server newsgroup, then. This newsgroup
is for a different program, Microsoft Access. Access is also a
database program and uses SQL, but the dialect is a bit different!

I'm GUESSING that you could do this with two EXISTS clauses, depending
on your table structure (which of course I do not know). Something
like

SELECT DISTINCT tablename.testdate
FROM tablename
WHERE (EXISTS (SELECT S1.Testdate FROM tablename AS S1
S1.StudentID = "Student1" AND S1.Grade = "A")
AND EXISTS((SELECT S2.Testdate FROM tablename AS S2
S2.StudentID = "Student2" AND S1.Grade = "B"))
OR
(EXISTS (SELECT S1.Testdate FROM tablename AS S3
S3.StudentID = "Student1" AND S3.Grade = "B")
AND EXISTS((SELECT S4.Testdate FROM tablename AS S4
S4.StudentID = "Student4" AND S1.Grade = "A"));


John W. Vinson[MVP]
 
Back
Top