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.

What mysql version do you use? Seems like you have 4.0 one :-S — zerkms
– zerkms, Commented Dec 16, 2010 at 0:44
What version of MySQL are you running? Subquery support was added in 4.1, in 2003. — Dan Grossman
– Dan Grossman, Commented Dec 16, 2010 at 0:44

zerkms · Accepted Answer · 2010-12-16 01:35:32Z

4

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

edited Dec 16, 2010 at 1:35

answered Dec 16, 2010 at 0:48

zerkms

256k73 gold badges447 silver badges551 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Aximili Over a year ago

Is there any equivalent query for version 3.23?

zerkms Over a year ago

@aximili: the latest 3.23 release was in September 11, 2003. I'm strongly recommend you to update it :-S

Dan Grossman Over a year ago

You have to rewrite this query as a join. It is fairly straightforward. But you should really take zerkms's advice and upgrade this server. You're a decade behind the times.

Aximili Over a year ago

lol thanks for the release date, nice to know! =p Sorry my query actually has an OR in it, see my updated question. Thanks zerkms and Dan!

Aximili Over a year ago

I have tried that as well as EXISTS, they don't seem to work either +_+ (You have an error in your SQL syntax near 'UNION SELECT c.*... at line 5)

|

johnny · Accepted Answer · 2010-12-16 00:47:09Z

3

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

answered Dec 16, 2010 at 0:47

johnny

19.9k54 gold badges169 silver badges271 bronze badges

4 Comments

Dan Grossman Over a year ago

As has already been covered in the comments. Don't steal zerkms's reputation.

johnny Over a year ago

@dan Zerkms should have put it in an answer. Also see the other response. You should chide that person also for being a thief. I was not aware there was anything wrong with learning from the other comments but having the courage to put it in an answer, right or wrong.

zerkms Over a year ago

@johnny: i don't like to guess. I'm answering only when I'm sure it is correct answer. In this particular case it can also be some kind of non-typed character right before select subquery.

johnny Over a year ago

@zerkms I was not saying you had to. I was responding more to dan not you. Sorry if you took offense. There have been times I should have put an answer instead of a comment.

Collectives™ on Stack Overflow

MySQL Subqueries Not Working, Generating Error Code 1064

2 Answers 2

7 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related