Posted by --CELKO-- on 03/20/06 16:59

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Here is my guess

CREATE TABLE Gradebook
(student_name CHAR(15) NOT NULL,
test_date DATETIME NOT NULL,
grade CHAR(1) NOT NULL,
PRIMARY KEY (student_name, test_date));

>> 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).<<

untested

WITH X(student_name, grade, test_date)
SELECT student_name, grade, test_date
FROM Gradebook
WHERE student_name IN ('student1', 'student2')
AND grade IN ('A', 'B')
SELECT
FROM X AS X1, X AS X2
WHERE X1.test_date = X2.test_date
AND X1.student_name <> X2.student_name
AND X1.grade <> X2.grade;

[Back to original message]