[SOLVED] sub query syntax error!

[Adam]

Hi guys.

 

MySQL is certainly not my strongest point! I have a problem in the syntax of this query:

 

delete from auth_user_temp aut
where aut.user_id in (
    select user_id from user_group
    where (userclass_id = 25 or userclass_id = 6)
    group by user_id
    having count(userclass_id) = 2
);

 

Basically the sub query returns records that have the userclass_id of 25 or 2, nothing else, and no less. The select sub query works fine by itself. I think the problem is using "aut.user_id in ..."

 

Does any body have an alternative or, perhaps be able to tell me why it's not working?

 

Thanks!

Adam

 

 

[Mchl]

Why do you have 'aut' in the first line?

 

Uhh...an alias of course

 

 

What is the error returned by MySQL?

[Adam]

"You have an error in your syntax near line 2" .. and shows virtually all the query! V. helpful!

 

Adam

[Adam]

Oops:

 

Basically the sub query returns records that have the userclass_id of 25 or 6 **

[fenway]

Oops:

 

Basically the sub query returns records that have the userclass_id of 25 or 6 **

So?

[Adam]

To avoid any "did you mean to put where (userclass_id = 25 or userclass_id = 2)" kind of responses...

 

Anyways, can anybody help me with the query?

 

Thanks,

Adam

 

[fenway]

What's the error now?

[Adam]

It's the same?

 

delete from auth_user_temp aut
where aut.user_id in (
    select user_id from user_group
    where (userclass_id = 25 or userclass_id = 6)
    group by user_id
    having count(userclass_id) = 2
);

 

Returns:

 

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 'where aut.user_id in (

    select user_id from user_group

    where (userclass_i' at line 2

 

Cheers,

Adam

[fenway]

Bizarre... and if you actually specify a new IDs directly it's fine?

[Adam]

Hah! Noo.. I was just making myself clearer in the explanation of the problem before - got our wires crossed I think!

[fenway]

which mysql version?

[Adam]

.. 4.1 currently but we're planning to upgrade soon. I'd still like to know if anyone has any suggestions as to why that wouldn't work, but I have made a work around in PHP to solve my problem now.

 

Thanks,

Adam

[fenway]

Can you post the table structures?

[Adam]

Hah, it's okay now. Problem has been fixed using PHP.. Database is pretty damn complex so not worth trying to wrap your head around it!

 

Cheers anyway though!