Re: Cross Reference Table Design and Query
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Tue, 17 Feb 2009 10:43:40 -0800 (PST)
Message-ID: <489db3b8-8413-4abd-820e-558b4eacab67_at_n21g2000vba.googlegroups.com>
On Feb 16, 4:14 pm, Michael Austin <maus..._at_firstdbasource.com> wrote:
> hotima wrote:
> > On Mon, 16 Feb 2009 07:12:33 -0800 (PST), Mark D Powell
> > <Mark.Pow..._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...- Hide quoted text -
>
> - Show quoted text -
Date: Tue, 17 Feb 2009 10:43:40 -0800 (PST)
Message-ID: <489db3b8-8413-4abd-820e-558b4eacab67_at_n21g2000vba.googlegroups.com>
On Feb 16, 4:14 pm, Michael Austin <maus..._at_firstdbasource.com> wrote:
> hotima wrote:
> > On Mon, 16 Feb 2009 07:12:33 -0800 (PST), Mark D Powell
> > <Mark.Pow..._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...- Hide quoted text -
>
> - Show quoted text -
OP posted a working query so all I did was provide the 'better' form for dynamic code generation and a suggestion of how to solve his second question. I think the effort displayed qualified for a hint.
IMHO -- Mark D Powell -- Received on Tue Feb 17 2009 - 12:43:40 CST