Mysql Query Error

[artie2004]

Does anyone know why i'm getting a syntax error message when i do the following query?

SELECT * FROM clients WHERE clno IN
(SELECT clno FROM firms WHERE city = 'leduc');

[orion_rus]

better type all php code
Maybe in the action of form this string u have a error

[artie2004]

The following are my SQL tables:

CREATE TABLE meds (
medid int NOT NULL auto_increment,
medname varchar(30),
qty int unsigned,
pilltype char(30),
priceperpill float(5,2) unsigned,
price float(5,2) unsigned,
PRIMARY KEY (medid)
);

CREATE TABLE meds_site (
medid char(3),
sitename char(30),
price float(5,2) unsigned,
PRIMARY KEY (medid,sitename)
);

My PHP code goes something like this:
$sitename = $_GET['sitename'];
$query = "SELECT * from meds where medid NOT IN (SELECT medid from meds_site where sitename='$sitename')";
$price_schedules = mysql_query($query);

$i = 0;
while($myrow = mysql_fetch_array($price_schedules)){

// Code to output my results here

}

When this script is executed, i am not getting any results when i should be.

And when i run this query directly from the SQL server i get the following error message:

ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp
onds to your MySQL server version for the right syntax to use near 'SELECT medid
from meds_site where
sitename='xyz.com')

[coopster]

Which version of MySQL are you running? Subqueries are only supported in MySQL >= 4.1.

Also, to get errors showing up in your scripts during development, you should turn on all error_reporting() [php.net].

[artie2004]

Thanks Coopster. My mysql is version 4.0.

[artie2004]

How can i do this query without using a subquery?

[coopster]

Use a LEFT JOIN and check for NULL values in the right hand table.

$query = " 
SELECT 
 * 
 FROM meds 
 LEFT JOIN meds_site 
 ON (meds.medid = meds_site.medid) 
 WHERE meds_site.medid IS NULL 
 AND sitename='$sitename' 
";

...I think I have that right...? Test it ;)

Rewriting Subqueries as Joins for Earlier MySQL Versions [dev.mysql.com]