Posted by Tom Moreau on 03/19/06 22:40

Another alternative, again untested:

select
TheDate
from
MyTable
where
Grade in ('A', 'B')
and Student in ('Student1', 'Student2')
group by
TheDate
having
sum (case
when Grade = 'A' and Student = 'Student1' then 1
when Grade = 'B' and Student = 'Student2' then 1
else 0 end) = 2
or sum (case
when Grade = 'A' and Student = 'Student2' then 1
when Grade = 'B' and Student = 'Student1' then 1
else 0 end) = 2

Same assumption as before.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:najTf.1845$ji6.54932@news20.bellglobal.com...
Please post your DDL + INSERT statements of your sample data. Here's an
untested solution:

select
TheDate
from
MyTable
where
Grade in ('A', 'B')
and Student in ('Student1', 'Student2')
group by
TheDate
having
(sum (case when Grade = 'A' and Student = 'Student1' then 1 else 0
end) = 1
and sum (case when Grade = 'B' and Student = 'Student2' then 1 else 0
end) = 1)
or (sum (case when Grade = 'A' and Student = 'Student2' then 1 else 0
end) = 1
and sum (case when Grade = 'B' and Student = 'Student1' then 1 else 0
end) = 1)

Assumes only one grade per student per date.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"肥權" <lokalun@gmail.com> wrote in message
news:1142790320.623759.278150@g10g2000cwb.googlegroups.com...
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

[Back to original message]