I have to rewrite a query with EXISTS clause for MySQL 3.23 since I cannot use MySQL 4.1.
The purpose of the query was to find all records from table MAIN that are annotated by a given set of numbers (number1, number2, ...). I have two tables: MAIN(ID, NAME) and A(ID, NUMBER) that stores the annotation relationship. The original query was:
SELECT NAME FROM MAIN WHERE
EXISTS (SELECT * FROM A WHERE MAIN.ID=A.ID AND NUMBER='number1') AND
EXISTS (SELECT * FROM A WHERE MAIN.ID=A.ID AND NUMBER='number2') AND ... so on for every number.
Is there any other way to write this query? Without the EXISTS clause?