Syntax in different versions of php mysql Client API

yebot · #1 (**permalink**) 11-28-05, 06:43

So I run this qry via phpmyadmin with php mysql Client API v. 3.23.49.

Code:

SELECT
`shownum`, `showdate`, `showdesc`, `venue`, `city`, `state`
FROM `table1`
WHERE `showdate`
NOT IN
(SELECT DISTINCT(`date`) FROM `table2`)

This works fine until I run the same qry on an identical database on my host's server with php mysql Client API v. 4.0.25. MySQL throws the following error:

#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 `date` FROM `table2 ` ) LIMIT 0, 30' at line

Is my syntax really incorrect or do the different versions dislike my 'nested' SELECT statement?

felixg · #2 (**permalink**) 11-28-05, 09:27

Quote:

Originally Posted by yebot

Is my syntax really incorrect or do the different versions dislike my 'nested' SELECT statement?

'Nested' SELECT statements are supported from MySQL 4.1 onwards.

--
felix

yebot · #3 (**permalink**) 11-28-05, 10:00

Thanks Felix.

I have Table1 where each record has a 'showdate'. Then in table2, there will be a series of records that have a 'date' that should correspond to a date in the Table1 'showdate'.

I want to run a qry that will show me all records in Table1 where Table1.showdate does not have at least one match in Table2.date.

Since my host is using MySQL 4.0.25, how can I accomplish this without using a 'nested' SELECT?

felixg · #4 (**permalink**) 11-28-05, 10:06

Quote:

Originally Posted by yebot

I want to run a qry that will show me all records in Table1 where Table1.showdate does not have at least one match in Table2.date.

Code:

SELECT
  Table1.*
FROM Table1
LEFT JOIN Table2 ON Table2.date = Table1.showdate
WHERE
  Table2.date IS NULL

--
felix

Internet Services

Web Development

Digital Marketing

Consumer Tech