How to implement this query in MySQL?

Question

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.

Dalmas · Accepted Answer · 2011-02-02 16:47:52Z

1

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'

answered Feb 2, 2011 at 16:47

Dalmas

26.6k9 gold badges71 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user565869 · Accepted Answer · 2011-02-02 16:45:09Z

0

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

answered Feb 2, 2011 at 16:45

user565869

Comments

Marcus · Accepted Answer · 2011-02-02 16:45:55Z

0

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)

answered Feb 2, 2011 at 16:45

Marcus

5,4873 gold badges36 silver badges54 bronze badges

Collectives™ on Stack Overflow

How to implement this query in MySQL?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related