Query Help, Maybe INTERSECT Workaround?

[Matt]

Alright so here's what I got (it's kinda lengthy so please bare with me). I've designed (and mostly implemented) an MSDS (Material Safety Data Sheets) search and retrieval web site for my company to use. It's implemented using PHP 5.0.4 and MySQL 4.1.13

Basically the way the tables are setup in the database are: datasheets - holds all the info about an MSDS (MSDS#, product name, chemical name, so forth and so on); loccodes - location ids, codes and names (plants); deptcodes - department ids, codes, location id to which the department belongs, and name; and locdepts which has an msds number and department id. So say a msds #4 is located at location 1 in department 3 (id #2) and at location 4 and department 2 (id #5) then there would be 2 rows in locdepts: row1 - #4, #2; row2 - #4, #5.

When a user searches for an MSDS he can search by product name, chemical name, etc. He can also search by locations/departments by selecting from a multiple select box that contains all locations/departments. He can choose whether the location/department search is an AND or OR search too. So if he chooses to search for Location1-Department2 and Location3-Department5 and has OR selected, he'll be search for all MSDS that are in either location1-department2 OR location3-department5. So you get the point. When the results are returned there will be a list of unique MSDS each containing the MSDS info and select box listing all the locations/departments that the MSDS is in.

Now the problem: If I search by OR, my query right now will return only the locs/depts that the user selected in the search box (so say the user searched for 2 locs/depts and an MSDS is in those 2 plus another, the result table will only have the 2 locs/depts selected by the user, not the 3rd. If I search by AND, my query will return nothing because it's expecting the dept ID to be both or all the ID's the user selected (ie. the user selects loc/dept #4 and loc/dept #5, the query will search the locdept table for an ID that equals both #3 and #4 which is impossible, they're two seperate rows in the locdept table) so basically I need help constucting a query that will help me do AND and OR's right. Here's my query as of now (as if the user was only search by location/department):

SELECT datasheets.*, Location, DeptID, Department
FROM datasheets
LEFT JOIN locdept ON locdept.MSDS=datasheets.MSDS
LEFT JOIN deptcodes ON deptcodes.ID=locdept.DeptID
LEFT JOIN loccodes ON loccodes.ID=deptCodes.LocID
WHERE ProductName REGEXP ".*.*"
AND ChemicalName REGEXP ".*.*"
AND Manufacturer REGEXP ".*.*"
AND (DeptID='3' AND DeptID='4') /* AND (DeptID='3' OR DeptID='4') */
AND Revised REGEXP '.*.*'
AND Status REGEXP '^.*'
ORDER BY ProductName

Now a co-worker of mine is working on this with me. She is fairly proficient with Oracle, but even she is having trouble with it. She found a solution that would work for Oracle using the INTERSECT function (solved the AND problem), unfortunately MySQL doesn't support INTERSECT yet. Is there a workaround for it? Any ideas on how to make this work will be GREATLY appreciated! (We will erect statues in your name and set them on our desks!)