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

[John Logan]

----- Original Message -----
From: "Anthony Baratta" <Anthony at Baratta.com>

> 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.

<snip>

Without having a db to test this, I think this will work. You can join to
tblCollegeMajors more than once.... This is probably less costly than
multiple subqueries, but YMMV. I'd want to see the query plan before making
a commitment.

With this sort of thing, I'd probably give up and create a stored procedure,
since you'd have to include/exclude the 2nd & 3rd JOINs based on the number
of parameters, or assume a fixed list and handle null parameters in your
WHERE clause.... (Mmmmmmm. Dynamic SQL!)

SELECT ColumnOne, ColumnTwo, ColumnThree
FROM tblCollegesSearch
    JOIN tblCollegeMajors CM1
       ON (CM1.CollegeID = tblCollegesSearch.CollegeID)
    JOIN tblCollegeMajors CM2
      ON (CM2.CollegeID = tblCollegesSearch.CollegeID)
    JOIN tblCollegeMajors CM3
      ON (CM3.CollegeID = tblCollegesSearch.CollegeID)
WHERE
         CM1.MRMajorID = '1'
AND CM2.MRMajorID = '2'
AND CM3.MRMajorID = '3'
GROUP BY RegionID, AcceptanceRateCategory
ORDER BY ColumnOne, ColumnTwo desc

Cheers,

John Logan
web ::: http://www.continuity.nu/
mail ::: john at continuity.nu