Page 1 of 1

Need help with MySQL query that simulates INTERSECT

Posted: Sun Jun 03, 2007 9:07 pm
by SearchEngineNightmare
First of all, let me say that I didn't design the underlying database!!!

I am working on a web-based search tool that will use PHP to search for customers that are active in 2 markets specified by the user.

If the user inputs $market1='chemical' and $market2='petroleum'

then I will send a query via PHP to the MySQL database to find customers active in BOTH markets.


Locations

+----+-------------+------+
| id | city | st |
+----+-------------+------+
| 1 | Denver | CO |
| 2 | Anchorage | AK |
| 3 | New Orleans | LA |
| 4 | Galveston | TX |
+----+-------------+------+


Customers

+------+----------------------+-----------------------+
| id | name | market |
+------+----------------------+-----------------------+
| 1 | Denver Chemical | Raw Chemical Supply |
| 2 | Anchorage Petroco | Petroleum Engineering |
| 3 | Johnson Chemical | Chemical Engineering |
| 3 | Johnson Chemical | Petroleum Engineering |

| 4 | Exacting Engineering | Chemical Engineering |
+------+----------------------+-----------------------+



The problem is that I can't find a MySQL query that will perform what I need done.


I want the results to be:

3 | Johnson Chemical | New Orleans | LA |


UNION uses OR logic which is bringing back results for customers 1, 2 and 4 who are NOT active in BOTH markets.


SELECT DISTINCT Customers.id, name, city, st FROM Customers, Locations
WHERE market LIKE '%chemical%' AND Customers.id=Locations.id
UNION
SELECT DISTINCT Customers.id, name, city, st FROM Customers, Locations
WHERE market LIKE '%petroleum%' AND Customers.id=Locations.id;

+------+----------------------+-------------+------+
| id | name | city | st |
+------+----------------------+-------------+------+
| 1 | Denver Chemical | Denver | CO |
| 3 | Johnson Chemical | New Orleans | LA |
| 4 | Exacting Engineering | Galveston | TX |
| 2 | Anchorage Petroco | Anchorage | AK |
+------+----------------------+-------------+------+


INTERSECT does not work in MySQL :banghead:

Do I need some sort of join? What syntax do I use?

Posted: Sun Jun 03, 2007 11:07 pm
by John Cartwright

Code: Select all

SELECT * FROM `customers` INNER JOIN `locations` USING (id) WHERE `customers`.`market` = 'Chemical Engineering' AND 'Petroleum Engineering'
Do I need some sort of join? What syntax do I use?
You already were doing a join, although it is just different from doing it the way I have done. Your problem was simply you wanted to use the AND logical operator instead of or, and you could also do without the UNION all together.