doing an intersect on mysql

Question

User-395883933 posted

I need to perform an intersect between these two queries to find out if some link has both status:

1- SELECT link as Active FROM monitor WHERE status = 1

2-SELECT link as Inactive FROM monitor WHERE status = 2

How can I do this with mysql?

Answer 1

User960609757 posted

Hi,

MySQL doesn't support the set operator INTERSECT

EXISTS function provides a simple way to find intersection between tables (INTERSECT operator from relational model).

If we have table1 and table2, both having id and value columns, the intersection could be calculated like this:

SELECT * FROM table1 WHERE EXISTS(SELECT * FROM table2 WHERE table1.id=table2.id AND table1.value=table2.value)


and also check this url,
http://gablog.eu/online/node/33