[thelist] SQL In need of Optimization....
Anthony Baratta
Anthony at Baratta.com
Fri Jun 29 00:31:09 CDT 2001
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