Hi all,

I have a cross-reference table which stores student class enrollment
info:

Table Name: ENROLLMENT_XREF

XREFID STUDENT CLASS
------ ------- -----
1 MARY MATH
2 MARY SCIENCE
3 MARY COOKERY
4 JIM MATH
5 JIM AUTO-REPAIR
6 JIM ENGLISH
7 JOHN COOKERY
8 JOHN ENGLISH
9 JOHN SCIENCE

The original table uses studentID and classID instead of real name;
however I put the full name in here in order to make thing more easily
to understand....

If I want to know which students have enrolled in MATH, the query is
SELECT STUDENT FROM ENROLLMENT_XREF WHERE CLASS='MATH'

If I want to know which students have enrolled in MATH and COOKERY:
SELECT STUDENT FROM
ENROLLMENT_XREF table1,
(SELECT STUDENT FROM ENROLLMENT_XREF WHERE CLASS='MATH') view1
WHERE
view1.STUDENT=table1.STUDENT AND
table1.CLASS='COOKERY';

But what is the query for knowing which students has enrolled in MATH,
COOKERY and SCIENCE ?

Yes I can further add extra in-line view based on the last query;
however my requirement is to build this query dynamically. Also the
number of classes can be included and excluded, like:
Find out which students have enrolled in MATH but not SCIENCE.

Do I need to create another table to do back-referencing ? Or I simply
go the wrong direction in building the query ?

Any input or pointer is absolutely welcome. TIA !!

On Feb 16, 6:39*am, hotima <hot... (AT) spam-trap-hotmail (DOT) com> wrote:
OP also posted to server. Responses exist there.

--