469,271 Members | 1,707 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

INTERSECT alternative in MySQL

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.
Apr 10 '07 #1
2 4675
code green
1,726 Expert 1GB
Can't promise this is exactly what you want but should point you in right direction.
Expand|Select|Wrap|Line Numbers
  1. SELECT agent_code,city_code FROM cust_table 
  2. JOIN agent_table USING (cust_id)
  3. GROUP BY city_code
Apr 11 '07 #2
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;
Apr 11 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Nick | last post: by
3 posts views Thread by prathamesh.deshpande | last post: by
1 post views Thread by kadhir34 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.