Cross Reference Table Design and Query

From: hotima <hotima_at_spam-trap-hotmail.com>
Date: Mon, 16 Feb 2009 19:13:34 +0800
Message-ID: <1dhip45gihlcc61i9nvca8ffo8s8696qje_at_4ax.com>



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 !! Received on Mon Feb 16 2009 - 05:13:34 CST

Original text of this message