CODEWITHSUNDEEP
mysqldatabase
Rajasekar
Rajasekar

Reputation: 18948

How to implement this query in MySQL?

My query is very similar to this.

(SELECT emailid FROM usereducation WHERE presfuncarea = '$funcarea') 
intersect
(SELECT emailid FROM userprofession WHERE totexpyear >= '$minexp')

Since MySQL does not support intersect, I have to find the right solution.

Upvotes: 2

Views: 69

Answers (3)

Dalmas
Dalmas

Reputation: 26547

This should do the job :

SELECT p.emailid
FROM usereducation e JOIN userprofession p ON p.emailid = e.emailid
WHERE e.presfuncarea = '$funcarea'
AND p.totexpyear >= '$minexp'

Upvotes: 1

Marcus
Marcus

Reputation: 5447

From: http://www.bitbybit.dk/carsten/blog/?p=71

An INTERSECT is simply an inner join where we compare the tuples of one table with those of the other, and select those that appear in both while weeding out duplicates. So

SELECT member_id, name FROM a
INTERSECT
SELECT member_id, name FROM b

can simply be rewritten to

SELECT a.member_id, a.name
FROM a INNER JOIN b
USING (member_id, name)

Upvotes: 0

user565869
user565869

Reputation:

MySQL supports EXISTS. This should work:

SELECT UE.emailid
FROM usereducation AS UE
WHERE UE.presfuncarea = '$funcarea'
  AND NOT EXISTS
    (
    SELECT * FROM userprofession AS UP
    WHERE UE.emailid = UP.emailID AND UP.totexpyear >= '$minexp'
    )

Upvotes: 0

Related Questions