CODEWITHSUNDEEP
sqlmysqlmysql-error-1064
Aximili
Aximili

Reputation: 29464

MySQL Subqueries Not Working, Generating Error Code 1064

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.

Upvotes: 2

Views: 1036

Answers (2)

zerkms
zerkms

Reputation: 254906

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

Upvotes: 4

johnny
johnny

Reputation: 19735

There is nothing wrong with your query. It is the version of MySQL that is more than likely your problem.

Upvotes: 3

Related Questions