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
Do I need some sort of join? What syntax do I use?