Re: Cross Reference Table Design and Query

From: Palooka <nobody_at_nowhere.com>
Date: Tue, 17 Feb 2009 18:46:59 +0000
Message-ID: <ACDml.2070$qy7.290_at_newsfe30.ams2>

Michael Austin wrote:

> hotima wrote:

>> 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!!

>
> and you may have just made his grade...

Exactly. Well put.

Palooka Received on Tue Feb 17 2009 - 12:46:59 CST