473,241 Members | 3,769 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 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
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 4914
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

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

Similar topics

6
by: Nick | last post by:
I have three tables books (id, title, author) authors (id, name) authors_groups (author_id, group_id) I have a query that searches for authors who must be in a least one subgroup of two...
1
by: sunaina | last post by:
I am doing the following query using intersect but gives me an error 'error in sql syntax. I tried using join as well but gives me similar error. In the following code still has intersect just to...
1
by: Xpertboy | last post by:
i am trying to execute following code in asp var strSQL1="select Model from Phones where Brand='"+ brand +"'"; var strSQL2="select Model from Phones where Model='"+ model +"'"; var...
3
by: prathamesh.deshpande | last post by:
Hi, I am facing problem while excuting a mysql query. My problem is as follows- I have two tables object_1 and object_2. The structure of object_1 is object_id name address ------------...
3
by: bzb | last post by:
hi ALL, I have a situation to use INTERSECT in my query, but INTERSECT is not supported in "mysql 4.1.15". Can anyone explain me how to tackle this problem..... ! Also, is there...
0
by: egur | last post by:
Hi, I'm looking for reliable MySQL version (for Windows) that supports "EXCEPT" and "INTERSECT" execution. Would somebody recommend such version (and corresponding client), please?
1
by: maddamon | last post by:
My problem looks like that: SELECT * FROM `table` where `attribute` = '24' INTERSECT SELECT * FROM `table` where `attribute` = '16'; and my 'table' structure is :...
1
by: kadhir34 | last post by:
Can any one give an alternative to this oracle query in mysql Select s.EMP_CODE,fname from profilesys.skill_emp_master P join profilesys.skill_master_new R on ( P.Skill_id = r.skill_id) join...
1
by: b00010783 | last post by:
Hi all, I have the table below. +--------+---------+-------------+--------------------------------+-------+----+ | parent | country | city | hotel | stars | id | ...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...

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.