mysql equivalent of the intersect comman

[Anon]

hi,
does anyone know any mysql workarounds for the intersect command? apparently, mysql still doesn't support this command so I can't use "select from table where job='clerk' intersect select from table where job='salesman'".
if anyone has come across this problem and has a solution for mysql, please post it. thanks.
you can read my earlier post for full details: http://www.phpbuilder.com/forum/read.php3?num=5&id=4861&loc=0&thread=4861

[Anon]

Not tested:

If every person has a unique name, and every person is listed exactly once for each job:

select count(),name from table where job="salesman" or job="clerk" group by name having count()==2;

[Anon]

from
http://www2.rent-a-database.de/mysql/mysql-51.html

A^A (intersection):

SQL> SELECT NAME FROM course_1 INTERSECT SELECT NAME FROM course_2

substitute by

SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;

A/B ("one side" intersection):

SQL> SELECT NAME FROM course_1 MINUS SELECT NAME FROM course_2

substitute by

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id IS NULL

I used it successfully.

o.k.

[[deleted]]

Note:
these examples are NOT a valid replacement for INTERSECT/MINUS etc.

They just happen to give the same result in the given examples.