|
|
Posted by avode on 03/21/06 09:57
FRANKLIN,
i repeat the sentence about the necessity to post ddl and sample data
and want to suggest the following solution of your problem:
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));
INSERT INTO Gradebook
SELECT 'Student1', '2006-01-01', 'A' UNION ALL
SELECT 'Student2', '2006-01-01', 'B' UNION ALL
SELECT 'Student3', '2006-01-01', 'A' UNION ALL
SELECT 'Student1', '2006-01-03', 'C' UNION ALL
SELECT 'Student2', '2006-01-03', 'A' UNION ALL
SELECT 'Student3', '2006-01-03', 'B' UNION ALL
SELECT 'Student1', '2006-01-07', 'A' UNION ALL
SELECT 'Student2', '2006-01-07', 'C' UNION ALL
SELECT 'Student3', '2006-01-07', 'A' UNION ALL
SELECT 'Student1', '2006-01-09', 'A' UNION ALL
SELECT 'Student2', '2006-01-09', 'B';
SET STATISTICS IO ON;
-- SELECT DISTINCT G1.test_date
-- FROM Gradebook AS G1, Gradebook AS G2
-- WHERE G1.test_date = G2.test_date
-- AND ( ( G1.student_name = 'Student1' AND G1.grade = 'A'
-- AND G2.student_name = 'Student2' AND G2.grade = 'B')
-- OR( G1.student_name = 'Student2' AND G1.grade = 'B'
-- AND G2.student_name = 'Student1' AND G2.grade = 'A'));
SELECT G1.test_date
FROM Gradebook AS G1
WHERE G1.student_name = 'Student1'
AND G1.grade IN('A', 'B')
AND EXISTS(SELECT *
FROM Gradebook AS G2
WHERE G2.student_name = 'Student2'
AND G2.test_date = G1.test_date
AND G2.grade = CASE G1.grade
WHEN 'A' THEN 'B'
WHEN 'B' THEN 'A' END);
SET STATISTICS IO OFF;
DROP TABLE Gradebook;
---
Andrey Odegov
avodeGOV@yandex.ru
(remove GOV to respond)
Navigation:
[Reply to this message]
|