Re: Cross Reference Table Design and Query
From: Michael Austin <maustin_at_firstdbasource.com>
Date: Mon, 16 Feb 2009 15:14:13 -0600
Message-ID: <IGkml.12593$W06.9379_at_flpi148.ffdc.sbc.com>
hotima 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!!
Date: Mon, 16 Feb 2009 15:14:13 -0600
Message-ID: <IGkml.12593$W06.9379_at_flpi148.ffdc.sbc.com>
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... Received on Mon Feb 16 2009 - 15:14:13 CST