472,133 Members | 1,190 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,133 software developers and data experts.

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

(2) agent_table

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)
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 4778
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
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

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.