Query for Cross Reference Table

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 wrote:
> 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 !!

OP also posted to server. Responses exist there.

--