Convert Intersect from Oracle to MySQL

Question

Hello I am trying to convert the following from oracle to Mysql as the result of using the INTERSECT with is not supported by Mysql. I have attempted to do several joins and still have no luck. Oracle Query:

select DISTINCT company_name from employee
inner join works on
works.Lastname=employee.Lastname AND
works.Firstname=employee.Firstname AND
works.MidInitial=employee.MidInitial
where employee.city='New York'
INTERSECT
select DISTINCT company_name from employee
inner join works on
works.Lastname=employee.Lastname AND
works.Firstname=employee.Firstname AND
works.MidInitial=employee.MidInitial
where employee.city='Seattle'

Thanks!

You should be check this link http://stackoverflow.com/questions/2621382/alternative-to-intersect-in-mysql — Mukesh Kalgude, Jul 10 '15 at 04:42
I'm not familiar with intersect, but it looks like you could achieve something similar using Union — scrowler, Jul 10 '15 at 05:46

score 0 · Accepted Answer · answered Jul 10 '15 at 04:41

In your case (all the rows are unique) the following approach should work:

SELECT x.company_name FROM
(select DISTINCT company_name from employee
 inner join works on
 works.Lastname=employee.Lastname AND
 works.Firstname=employee.Firstname AND
 works.MidInitial=employee.MidInitial
 where employee.city='New York') x
INNER JOIN
(select DISTINCT company_name from employee
 inner join works on
 works.Lastname=employee.Lastname AND
 works.Firstname=employee.Firstname AND
 works.MidInitial=employee.MidInitial
 where employee.city='Seattle') y
USING (company_name)

Convert Intersect from Oracle to MySQL

1 Answers1