What's wrong with this subquery?

[KidViciou$]

i can't figure it out, it looks simple enough:

Error

SQL-query :

SELECT a.num
FROM surveys a
WHERE a.num
IN (
SELECT b.num
FROM surveys b
)
LIMIT 0 , 30

MySQL said:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT b.num
FROM surveys b ) LIMIT 0, 30' at line 5

 

[Snapster]

What version of mysql do you have? Only versions mysql 4.1 and greater supports subqueries iirc.

 

[KidViciou$]

dammit, 8-95.com is using 4.0.17 :|:|:|

i submitted a ticket to them and this is their response:

Nope... should work fine if the syntax is correct



i'm getting dismayed with the support ive been receiving, the response time has been quite slow

 

[lansalot]

That statement makes no sense.

Select column from table where number exists somewhere in column in table?

You will get the same result with (assuming LIMIT is in the right place, not savvy with mysql dialect):

SELECT a.num
FROM surveys a
LIMIT 0 , 30

 

[Snapster]

n/m

 

[KidViciou$]

Originally posted by: lansalot
That statement makes no sense.

Select column from table where number exists somewhere in column in table?

You will get the same result with (assuming LIMIT is in the right place, not savvy with mysql dialect):

SELECT a.num
FROM surveys a
LIMIT 0 , 30

exactly, which means it's guaranteed to work syntactically although it doesn't do anything different functionally

but i found out hte problem is that my host is running 4.0 of mysql and subqueries aren't implemented til 4.1 :|

 

[screw3d]

Originally posted by: KidViciou$

Originally posted by: lansalot
That statement makes no sense.

Select column from table where number exists somewhere in column in table?

You will get the same result with (assuming LIMIT is in the right place, not savvy with mysql dialect):

SELECT a.num
FROM surveys a
LIMIT 0 , 30

exactly, which means it's guaranteed to work syntactically although it doesn't do anything different functionally

but i found out hte problem is that my host is running 4.0 of mysql and subqueries aren't implemented til 4.1 :|

Time to switch host then.. plenty of other companies waiting for your business

 

[KidViciou$]

well i only need hosting for 2 more weeks, my project will be done then. but i will be writing an email to gary @ dixiesys about the problems i encountered

 

[kamper]

Originally posted by: KidViciou$

Originally posted by: lansalot
That statement makes no sense.

Select column from table where number exists somewhere in column in table?

You will get the same result with (assuming LIMIT is in the right place, not savvy with mysql dialect):

SELECT a.num
FROM surveys a
LIMIT 0 , 30

exactly, which means it's guaranteed to work syntactically although it doesn't do anything different functionally

But his point was that the subquery was useless so you don't need it. Or was the OP just a simplified version for the purpose of demonstration? Although this may be pointless, I'll just add that having a limit without an order by is somewhat pointless (or at least just weird).

 

[KidViciou$]

i did the useless subquery to show that even extremely simple subqueries weren't working. the problem was resolved to be the version of mysql being used

and the web interface i have with the database automatically inserts the LIMIT statement

 

[kamper]

Ah. I knew the cause of the problem was found, just wasn't sure if you were still looking for a way around it. And I see about it just being a demo (and the limit statement). I guess what I'm saying is: I understand now

 

[oog]

i am also hosted at 8-95.com and ran into this same issue with the version of mysql not supporting subqueries. i ended up rewriting the query to avoid the subquery (in my case, it was a NOT IN subquery). i did an outer join and looked for NULLs.

 

[KidViciou$]

perhaps i should have done that oog, as i really did need a not in subquery, but i actually re-wrote the query as 2 queries and then ran for loops since performance wasn't an issue. i should have relied on my DBA training and just done the outer join like u did, but alas, i have forgotten much of what i learned

 

[oog]

an outer join and a NOT NULL will be much slower than the subquery, but i'm guessing it's faster than looping through the results. it can't be that hard to switch out your code. if you post your table definitions and what you're trying to do, i can help write it.

 

[KidViciou$]

thanks for the offer, but it's just for a class project, so it's not that important. this makes me realize how much i forgot since i got my certification though. i really should start brushing up on my dba stuff once graduation nears

it would be more trouble for me to test it and make sure the new code works