Select max subquery

[dhipp]

I'm not sure why this query is not working on MySql 5.0.24 as it works on MySql 4.1.14.  I am trying to create a simple subquery as:

 

SELECT a.login_id

FROM disco_vendor_ids a

WHERE a.vendor = 'XYZ'

AND a.effdt = (SELECT max(b.effdt)

FROM disco_vendor_ids b

WHERE a.vend_id = b.vend_id)

 

When I try to run this query in 5.0.24 I keep getting:

#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 max(b.effdt)

FROM disco_vendor_ids b

WHERE a.vend_id ...

 

I don't think it's a syntax error as the query runs in 4.1.14.  I have only recently set this database up in 5.0.24 perhaps there is a setting somewhere that is not correct?  Any help would be greatly appreciated.

 

Thank you.

[clearstatcache]

if u want kindly post ur table structure and the result u want....id prefer join compared to subquery...

[Illusion]

I guess, Ur alias tables 'a' and 'b' doesn't have the column vend_id then the condition WHERE a.vend_id = b.vend_id doesn't work so u need to retrieve the that column from both the tables and then use them.

[dhipp]

clearstatcache - The table structure is:

 

Table name: disco_vendor_ids

 

Field      Type

-------    -----

vend_id  int(5)

effdt      date

vendor    varchar(15)

descr      varchar(35)

login_id    varchar(30)

passwd    varchar(30)

 

 

I think there might be difference between MySql 4.1.14 and 5.0.24 and the way queries are parsed?  I'm not sure what "features" were added for 5.0.24, but the subquery worked in 4.1.14.  Thanks for your help so far.

[fenway]

I agree with an earlier reply, JOINing a derived table would be preferable.

[clearstatcache]

show ur table data or values also nd the result u want from the query...pls..