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

[rudy]

>Not sure if this uses Oracle-only features or not...

hi john

INTERSECT is standard sql-92 (although not all databases support it)

it's also the cleanest-looking and easiest to understand of the solutions

anthony has an additional challenge -- generalizing from 3 majors (used in
all the examples so far) to any number, like you might get from <select
multiple> or from checkboxes...

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

yes, you can generate as many intersect subqueries on the fly as you need,
but that's awkward (because if only one is selected, you gots nothing to
intersect with -- not a huge programming hurdle, but one you have to allow
for in any case)

how about this -- let the person check off as many majors as they want,
from 1 up, and pick all the colleges that have X number or more of them

so if i check off 4 majors, i might want to see a list of colleges that
offer at least three of my four choices...

select CollegeID , count(*)
    from tblCollegeMajors
  where MajorID in ( #majorList# )
  group by CollegeID
    having count(*) >= #X#

the nice part about this is that it is so amenable to just plunking in a
comma delimited list from the web page

then to get the college info,

   select obladi, oblada
     from tblCollegeSearch
   where CollegeID in
      ( select CollegeID , count(*)
           from tblCollegeMajors
         where MajorID in ( #majorList# )
         group by CollegeID
           having count(*) >= #X# )

it's also easy to add other college conditions to this query with a
subsequent AND in the WHERE clause


rudy