PHP 5 / MySQL 5 subquery problem

[Diogenes]

Kind readers,

I am currently developing an app with PHP 5 & MySql 5.

The following select statement works as expected from
the MySql command line but produces an error when run from PHP.

SELECT pkey, Name FROM tracks WHERE artist="Rolling Stones"
AND pkey NOT IN (SELECT Song FROM requests WHERE client="jim")

The error produced by PHP is

"Query failed: 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 ')' at line 1"

I've done a workaround by substituting the pkey values in
the parentheses in place of the subquery SELECT statement,
but I can't figure out why the above query does not work in
PHP but does in MySQL.

The versions of each app read as follows ...

>mysql -V

mysql Ver 14.12 Distrib 5.0.22, for Win32 (ia32)

>php -v

PHP 5.2.0-dev (cli) (built: Jul 19 2006 16:20:24)
Copyright (c) 1997-2006 The PHP Group
Zend Engine v2.2.0, Copyright (c) 1998-2006 Zend Technologies

Any insight would be much appreciated.

Cheers
Jim

[Captain Paralytic]

Diogenes wrote:

Kind readers,

I am currently developing an app with PHP 5 & MySql 5.

The following select statement works as expected from
the MySql command line but produces an error when run from PHP.

SELECT pkey, Name FROM tracks WHERE artist="Rolling Stones"
AND pkey NOT IN (SELECT Song FROM requests WHERE client="jim")

The error produced by PHP is

"Query failed: 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 ')' at line 1"

I've done a workaround by substituting the pkey values in
the parentheses in place of the subquery SELECT statement,
but I can't figure out why the above query does not work in
PHP but does in MySQL.

The versions of each app read as follows ...

>mysql -V

mysql Ver 14.12 Distrib 5.0.22, for Win32 (ia32)

>php -v

PHP 5.2.0-dev (cli) (built: Jul 19 2006 16:20:24)
Copyright (c) 1997-2006 The PHP Group
Zend Engine v2.2.0, Copyright (c) 1998-2006 Zend Technologies

Any insight would be much appreciated.

Cheers
Jim

It is almost always more efficient to use the LEFT JOIN alternative for
this task

[Diogenes]

Captain Paralytic wrote:

Diogenes wrote:

>>
SELECT pkey, Name FROM tracks WHERE artist="Rolling Stones"
AND pkey NOT IN (SELECT Song FROM requests WHERE client="jim")

It is almost always more efficient to use the LEFT JOIN alternative for
this task

I'm a little naive on SQL. Could you redo the above SELECT
statement with your suggested alternative? Then I'll try it
in PHP.

Cheers
Jim

[Captain Paralytic]

Diogenes wrote:

Captain Paralytic wrote:

Diogenes wrote:

>
SELECT pkey, Name FROM tracks WHERE artist="Rolling Stones"
AND pkey NOT IN (SELECT Song FROM requests WHERE client="jim")

It is almost always more efficient to use the LEFT JOIN alternative for
this task

I'm a little naive on SQL. Could you redo the above SELECT
statement with your suggested alternative? Then I'll try it
in PHP.

Cheers
Jim

Without seeing the scheme and some sample data I can't be sure that
I've captured all the nuances of what you want, but try:

SELECT pkey, Name FROM tracks
LEFT JOIN requests ON tracks.pkey = requests.Song AND requests.client =
"jim"
WHERE tracks.artist="Rolling Stones" AND requests.Song IS NULL

[Diogenes]

Kudos to the Captain! That works beautifully both on the
command line and in PHP!

Thank you! Thank you! Thank you!

Cheers
Jim

Captain Paralytic wrote:

Diogenes wrote:

>>Captain Paralytic wrote:

>>>Diogenes wrote:

SELECT pkey, Name FROM tracks WHERE artist="Rolling Stones"
AND pkey NOT IN (SELECT Song FROM requests WHERE client="jim")

It is almost always more efficient to use the LEFT JOIN alternative for
this task

I'm a little naive on SQL. Could you redo the above SELECT
statement with your suggested alternative? Then I'll try it
in PHP.

Cheers
Jim

Without seeing the scheme and some sample data I can't be sure that
I've captured all the nuances of what you want, but try:

SELECT pkey, Name FROM tracks
LEFT JOIN requests ON tracks.pkey = requests.Song AND requests.client =
"jim"
WHERE tracks.artist="Rolling Stones" AND requests.Song IS NULL