Query issue

pirox · #1 (**permalink**) 06-12-06, 06:33

Hello all,

I'm new here and I have a question. There is a query im running which fails to execute on our servers. We run MYSQL 5 locally, while our server runs 4.0.25. What puzzels me is why the code is not running. The code is as follows:

select DISTINCT id from [] where []=1 and NOT EXISTS (select [] from [] where []=[]);

but the server tells us the following :

#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 'EXISTS (select [] from [] where []=[]) LIMIT

Can someone tell me whether mysql 4 supports NOT EXISTS? We even tried NOT IN, but no success...

Thank You.

Pirox

r937 · #2 (**permalink**) 06-12-06, 07:10

subqueries are not supported until version 4.1

Pat Phelan · #3 (**permalink**) 06-12-06, 11:17

I think what you need is more like:

Code:

select id
   from []
   LEFT JOIN []
      ON ([] = [])
   where 1 = []
      AND [] IS NULL
   GROUP BY [];

This should work much better in MySQL.

-PatP

Brett Kaiser · #4 (**permalink**) 06-12-06, 11:26

what the hell i sthat?

pootle flump · #5 (**permalink**) 06-12-06, 11:41

Doesn't []=[] always evaluate to true? I'm gonna plump for:

Code:

SELECT DISTINCT id 
FROM [] 
WHERE []=1

Pat Phelan · #6 (**permalink**) 06-12-06, 11:54

Quote:

Originally Posted by pootle flump

Doesn't []=[] always evaluate to true? I'm gonna plump for:

Code:

SELECT DISTINCT id 
FROM [] 
WHERE []=1

Good catch on the [] = 1 part! I missed that, so I've amended my original suggestion.

I don't think it is safe to assume that [] will always equal [], so I think it is better left stated explicitly. If it isn't needed, I'm sure that it will get optimized out.

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech