Bug #35709 Error

• Submitted: 31 Mar 2008 14:22
• Modified: 31 Mar 2008 14:50
• Reporter: Dhirendra Jain
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4
• OS: Any
• CPU Architecture: Any
• Tags: Select Query Not Working

[31 Mar 2008 14:22] Dhirendra Jain

Description:
Error on select statement in mysql verion 4.

How to repeat:

I have sql Select statement in mysql verion 4 as below

select status from tblinterview where jobid = 5000125 and personid = 508603 and interviewid  = ( select max(interviewid) from tblinterview where jobid =  5000125 and personid =  508603 )

this give the error like --> syntax error near 'select max(interviewid).

here, the result of the inner query is used in where clause of the outer query.

Please not that the output of the inner select query 'select max(interviewid) from tblinterview where jobid =  5000125 and personid =  508603' is comes to NULL.

The above whole query runs/works successfully in mysql ver 5.0

Please Suggests any help for prior version i.e mysql 4.0

Thanks in advance.
Dhirendra.

[31 Mar 2008 14:50] Hartmut Holzgraefe

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

MySQL 4.0 does not support subqueries, see also:

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

In your case a simple

   select status 
     from tblinterview 
    where jobid = 5000125 
      and personid = 508603 
 ORDER BY interviewid DESC
    LIMIT 1

would probably be sufficient assuming that "interviewid" is unique

Thank you for your interest in MySQL.