Re: Query for Cross Reference Table

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Mon, 16 Feb 2009 07:22:59 -0800 (PST)
Message-ID: <17494b86-625b-4797-8854-a3b4848b9c65_at_q25g2000vbn.googlegroups.com>

On Feb 16, 6:39 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 !!

OP also posted to server. Responses exist there.

--

Received on Mon Feb 16 2009 - 09:22:59 CST