Re: Cross Reference Table Design and Query

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Mon, 16 Feb 2009 07:12:33 -0800 (PST)
Message-ID: <72bc0dde-447a-48f2-8185-240e16dd4503_at_v38g2000yqb.googlegroups.com>

On Feb 16, 6:13 am, hotima <hot..._at_spam-trap-hotmail.com> 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 !!

You just use a join where you add the xref table once for every class required and filter each additional table for that class.

select t1.* from xref t1, xref t2, xref t3, ... where t1.student = t2.student

and     t1.student = t3.student
and     t1.class = 'MATH'
and     t2.class = 'SCIENCE'
and     t3.class = 'THIRDCLASS'

etc ...

For excluding a class I think I would try either a not in subquery of all students who are taking class X or an not exists subquery of the same.

HTH -- Mark D Powell -- Received on Mon Feb 16 2009 - 09:12:33 CST