Cross Reference Table Design and Query
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