INTERSECT alternative in MySQL

[ngenius]

Hello everyone, I am new to MySQL but just realized it does not support INTERSECT in MySQL 5.0 Community Server.

I have this problem to solve.
(1) cust_table
cust_id
city_code.

(2) agent_table
agent_code
cust_id

An agent may work for more than one customer.

My idea was to have two tables and use INTERSECT to produce a result that would list all agents that serve all customers in a particular city grouped by cities.

SELECT cust_id FROM cust_table where city_code IN (city1, city2)
INTERSECT
SELECT cust_id FROM agent_table;

But MySQL does not have INTERSECT so I am confused as to the solution.

[code green]

Can't promise this is exactly what you want but should point you in right direction.

SELECT agent_code,city_code FROM cust_table 
JOIN agent_table USING (cust_id)
GROUP BY city_code

[ngenius]

Thanks for the effort. The final query I got that did the job was so convoluted, I am still trying to understand it. ;-)

And sorry for the bother and not providing enough details.
I was basically looking for an alternative to the INTERSECT which is absent from MySQL implementatios.

SELECT DISTINCT a.aid, a.percent
FROM agent a
WHERE (SELECT COUNT(DISTINCT(o.cid))
FROM order1 o, customer c2
WHERE o.aid=a.aid
AND c2.cid=o.cid
AND c2.city='Dallas') = (SELECT COUNT(*)
FROM customer c
WHERE c.city='Dallas')
ORDER BY a.percent DESC;