MySQL Subqueries Not Working, Generating Error Code 1064

Question

This query doesn't work

SELECT * FROM Catalogue WHERE Catalogue.ID IN (
    SELECT ID_catalogue FROM Categories WHERE Categories.Product_type='xxx'
)

Error Code : 1064 You have an error in your SQL syntax near 'SELECT ID_catalogue FROM Categories WHERE Categories.Product_type='xxx' ) ' at line 2

Ok, that's because I am using a very old MySQL version.

What I am trying to get is

SELECT * FROM Catalogue WHERE Product_type='xxx' OR Catalogue.ID IN (
    SELECT ID_catalogue FROM Categories WHERE Categories.Product_type='xxx'
)

Is there any equivalent for that?

Thank you for all your comments.

Answer 1

If you're using mysql version <= 4.0 - then it is the reason, since subqueries were added in 4.1

     SELECT c.*,
            g.ID_catalogue
       FROM Catalogue c
  LEFT JOIN Categories g ON g.ID_catalogue = c.ID
                        AND g.Product_type='xxx'
     HAVING ID_catalogue IS NOT NULL
         OR Product_type = 'xxx'

You should add composite index ID_catalogue + Product_type for table Categories AND index Product_type for table Catalogue to have this query performed fast