[thelist] SQL In need of Optimization....

evolt@spinhead evolt at spinhead.com
Fri Jun 29 12:29:08 CDT 2001


Off the top of my head: Is it possible to create a view of the colleges that
offer both, and query that?

spinhead


----- Original Message -----
From: "Anthony Baratta" <Anthony at Baratta.com>
To: <thelist at lists.evolt.org>
Sent: Thursday, June 28, 2001 10:28 PM
Subject: [thelist] SQL In need of Optimization....


> Howdy...
>
> I've got an interesting Query I need to perform on a Database and the
route
> I've chosen most probably is the long way around to the answer. If someone
> has the time or inclination, any ideas would be appreciated:
>
> __Background__
>
> I have a table that holds information on what Majors a College is
offering.
> Basically there are two fields: CollegeID & MajorID. I'm using this data
as
> a "filter". Use selects what majors they want (Plus other criteria) and
> then they get a list of colleges that match their selections.
>
> __Challenge__
>
> My problem is that when someone selects more than one Major they would
like
> to see offered. We want to select ALL colleges that offer both (or more)
> Majors. This is not the case of a simple use of AND.
>
> If I use an AND:
>
> SELECT ColumnOne, ColumnTwo, ColumnThree FROM tblCollegesSearch
> WHERE
>     tblCollegesSearch.CollegeID IN
>     (SELECT distinct tblCollegeMajors.CollegeID
>     FROM tblCollegeMajors WHERE
>        tblCollegeMajors.MajorID = '1' AND
>        tblCollegeMajors.MajorID = '2' AND
>        tblCollegeMajors.MajorID = '3'
>     )
> GROUP BY ColumnOne, ColumnTwo, order by 1,2 desc
>
> *or this way*
>
> SELECT ColumnOne, ColumnTwo, ColumnThree FROM tblCollegesSearch
> JOIN tblCollegeMajors
>    ON (tblCollegeMajors.CollegeID = tblCollegesSearch.CollegeID)
> WHERE
>     tblCollegeMajors.MRMajorID = '1' AND
>     tblCollegeMajors.MRMajorID = '2' AND
>     tblCollegeMajors.MRMajorID = '3'
> GROUP BY RegionID, AcceptanceRateCategory order by 1,2 desc
>
>
> we get zero results. Which when you look at the way AND works, makes
sense.
> So what we really need to do is create little groups of colleges that
offer
> each major and select among those that belong to all groups. This is what
I
> came up with:
>
> SELECT ColumnOne, ColumnTwo, ColumnThree FROM tblCollegesSearch
> WHERE
>     tblCollegesSearch.CollegeID IN
>     (select distinct tblCollegeMajors.CollegeID
>     from tblCollegeMajors WHERE
>        tblCollegeMajors.CollegeID IN
>        (select tblCollegeMajors.CollegeID
>         FROM tblCollegeMajors WHERE
>            tblCollegeMajors.MajorID = '1')
>        AND
>        tblCollegeMajors.CollegeID IN
>        (select tblCollegeMajors.CollegeID
>         FROM tblCollegeMajors WHERE
>            tblCollegeMajors.MajorID = '2')
>        AND
>        tblCollegeMajors.CollegeID IN
>        (select tblCollegeMajors.CollegeID
>         FROM tblCollegeMajors WHERE
>            tblCollegeMajors.MajorID = '3')
>     )
> GROUP BY ColumnOne, ColumnTwo, order by 1,2 desc
>
>
> Now this returns (I think) pretty good data!! Whee! ;-) But I can't help
> but wonder if there is a better way. Thoughts, ideas, and comments
welcome.
> ---
> Anthony Baratta
> President
> Keyboard Jockeys
>
> Blatant Plug: Cool Jazz for a hot summer,
>      http://LisaMarie.Baratta.com
>





More information about the thelist mailing list