Page 1 of 1

help with SQL syntax error

Posted: Sun Apr 11, 2004 4:10 am
by davidklonski
Hello

I am new to SQL and I ran into a small syntax problem

I am trying to run the following SQL query which results in a syntax error which I cannot find:

SELECT COUNT(*) FROM individual_tbl WHERE (Last_name LIKE '%na%' AND ID IN (SELECT ID, Last_name FROM individual_tbl WHERE (Last_name LIKE 'N%')));

Here is the definition of individual_tbl:
CREATE TABLE `individual_tbl` (
`ID` int(11) unsigned NOT NULL auto_increment,
`Last_name` varchar(100) default '',
PRIMARY KEY (`ID`)
) TYPE=MyISAM;

can anyone see the problem?

Posted: Sun Apr 11, 2004 4:20 am
by andre_c
what database server are you using? (current stable version of MySQL doesn't support subqueries)

Posted: Sun Apr 11, 2004 4:24 am
by davidklonski
I am using version 4.0.17

Posted: Sun Apr 11, 2004 4:31 am
by andre_c
MySQL starts supporting subqueries with version 4.1 (still in alpha). To get around it you can use temporary tables.

Posted: Sun Apr 11, 2004 4:33 am
by davidklonski
can you give me a small example of how I can achieve this using temporary tables?

Posted: Sun Apr 11, 2004 4:39 am
by andre_c
Unfortunately, it will take two statements: create temp table, select what you need:

Code: Select all

CREATE TEMPORARY TABLE temp_table SELECT id, last_name FROM ... etc.
... then use that table on your select statement. The table will be deleted when the connection is closed