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

Ornstein, Ian IanOrnstein at NC.SLR.com
Fri Jun 29 08:52:12 CDT 2001


Alastair has my vote:

SELECT ColumnOne, ColumnTwo, ColumnThree FROM tblCollegesSearch
JOIN tblCollegeMajors
   ON (tblCollegeMajors.CollegeID = tblCollegesSearch.CollegeID)
WHERE  tblCollegesSearch.CollegeID IN
      ('1','2','3')    
GROUP BY RegionID, AcceptanceRateCategory order by 1,2 desc

HTH
- IanO -
Ian Ornstein - Programmer and Web Developer
IBM Global Services, Global AMS Delivery
(704) 509-8022 


-----Original Message-----
From: Alastair Murdoch [mailto:alastair at cubeit.co.uk]
Sent: Friday, June 29, 2001 5:08 AM
To: thelist at lists.evolt.org
Subject: RE: [thelist] SQL In need of Optimization....


forgive me, i'm still a SQL beginner, but whats wrong with doing  'IN
(1,2,3)' as a set, rather than an 'AND same statement in 1, AND same
statement in 2,.........

Basically you just feed your in statement all the values.

I'm suggesting it because I just finished something similar with postcodes
and areas, where selecting two or more areas had to list every project in
every postcode area.

Was that any use??

-------------------------------
A l a s t a i r   M u r d o c h
c o n s u l t a n t  @  c u b e

    [t:]  01292 27 00 99
    [m:]  07974 323 551
    [w:] www.cubeit.co.uk


-----Original Message-----
From: thelist-admin at lists.evolt.org
[mailto:thelist-admin at lists.evolt.org]On Behalf Of Anthony Baratta
Sent: 29 June 2001 06:29
To: thelist at lists.evolt.org
Subject: [thelist] SQL In need of Optimization....


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


---------------------------------------
For unsubscribe and other options, including
the Tip Harvester and archive of TheList go to:
http://lists.evolt.org Workers of the Web, evolt !





More information about the thelist mailing list