[thelist] SQL In need of Optimization....
rudy
r937 at interlog.com
Fri Jun 29 09:05:27 CDT 2001
>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
More information about the thelist
mailing list