WHERE IN statement isn't working correctly...

[tsyle]

Select U.user_id, U.Username, U.timestamp, U.account_type
From users U
Where U.user_id IN (Select T.user_id
From txnlog T
Where T.result = 'Cash Paid'
Group By T.user_id)

This is the code that I am working with. I don't know where i'm going wrong but PHPAdmin keeps telling me that I have a syntax error. I have copied the error below.

#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 T.user_id

I have no idea why this isn't working. When i use Where U.user_id IN ('1','2','3') it works... Please help
From txnlog T

 

[sleipnir214]

What version of MySQL are you running? Only MySQL versions 4.1 and newer support subqueries.

Take a look also at

http://dev.mysql.com/doc/refman/5.0/en/rewriting-subqueries.html

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[tsyle]

oh... My version is 4.0... if i update it will i lose all my tables?

 

[KarveR]

no, you will retain all info, just be sure to back up before .. just in case*.



*as we all know how things which can go badly wrong, always will.



______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[r937]

most subqueries can be rewritten as joins

select U.user_id
     , U.Username
     , U.timestamp
     , U.account_type
  from users U
inner
  join txnlog T
    on U.user_id = T.user_id
   and T.result = 'Cash Paid'
group 
    by U.user_id
     , U.Username
     , U.timestamp
     , U.account_type

r937.com | rudy.ca