SQL syntax issue

This query below has worked fine on most servers I've used, but one is throwing up an error. Doesn't seem to like the syntax. Is there a quick alternative to acheive the same result?

	
$qry="select * from rate_vars a where var_id in
(select b.var_id from topic_ratevars b where topic_id=$topic_id ) ORDER BY var_name";

PHP:

Query error: 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 b.var_id from topic_ratevars b where topic_id=22 ) ORDER

Code (markup):

Thanks for any help in advance.

 

First off, what's the reason for using the a and the b in this query? They both look unnecessary to me in this example.

I would try this:

SELECT * FROM rate_vars WHERE var_id IN
(SELECT var_id FROM topic_ratevars WHERE topic_id = '$topic_id')
ORDER BY var_name

Also, an inner join may be a quicker way to perform this query, but the above should work.

 

as mentioned by jestep, a join would fit better here..

SELECT * FROM rate_vars rv INNER JOIN topic_ratevars trv ON rv.var_id = trv.var_id WHERE trv.topic_id = '$topic_id' ORDER BY rv.var_name

Code (sql):

hope this helps

 

Subqueries are not supported in MySQL versions less than 4.1.

 

try out this query

$qry="select * from rate_vars a where a.var_id in
(select b.var_id from topic_ratevars b where b.topic_id=$topic_id ) ORDER BY a.var_name";

 

use mysql5.x that should resolve your problem as mwasif said

 

Thank you for everyone's help. Much appriciated.