Need help with MySQL query that simulates INTERSECT

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
SearchEngineNightmare
Forum Newbie
Posts: 2
Joined: Sun May 06, 2007 7:31 pm

Need help with MySQL query that simulates INTERSECT

Post 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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

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