Re: Cross Reference Table Design and Query
Date: Tue, 17 Feb 2009 02:12:51 +0800
Message-ID: <t0bjp4dqvvhqlia37b0r2d9sfa2t43gokv_at_4ax.com>
On Mon, 16 Feb 2009 07:12:33 -0800 (PST), Mark D Powell <Mark.Powell_at_eds.com> wrote:
>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 --
Thanks Mark !! You make my day!! Received on Mon Feb 16 2009 - 12:12:51 CST