[thelist] SQL In need of Optimization....
Joshua Olson
joshua at alphashop.net
Fri Jun 29 08:37:37 CDT 2001
What about something like
SELECT
ColumnOne,
ColumnTwo,
ColumnThree,
FROM tblCollegesSearch
WHERE
EXISTS
(SELECT Count(MajorID) AS MajorCount
FROM tblCollegeMajors.CollegeID
WHERE tblCollegeMajors.CollegeID = tblCollegesSearch.CollegeID
AND tblCollegeMajors.MajorID IN ('1', '2')
HAVING MajorCount >= 2)
GROUP BY ColumnOne, ColumnTwo, order by 1,2 desc
The real neat part about this, assuming I syntaxed it correctly for your db,
is that you can change the HAVING clause to represent how many of the majors
at minimum the college must carry. If they are interested in 3 programs but
the listed colleges only need have 2 of the three, list all three in the IN
clause, but only set the HAVING clause to >= 2.
HTH,
-joshua
More information about the thelist
mailing list