help with SQL syntax error

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

help with SQL syntax error

Post 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?
User avatar
andre_c
Forum Contributor
Posts: 412
Joined: Sun Feb 29, 2004 6:49 pm
Location: Salt Lake City, Utah

Post by andre_c »

what database server are you using? (current stable version of MySQL doesn't support subqueries)
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

Post by davidklonski »

I am using version 4.0.17
User avatar
andre_c
Forum Contributor
Posts: 412
Joined: Sun Feb 29, 2004 6:49 pm
Location: Salt Lake City, Utah

Post by andre_c »

MySQL starts supporting subqueries with version 4.1 (still in alpha). To get around it you can use temporary tables.
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

Post by davidklonski »

can you give me a small example of how I can achieve this using temporary tables?
User avatar
andre_c
Forum Contributor
Posts: 412
Joined: Sun Feb 29, 2004 6:49 pm
Location: Salt Lake City, Utah

Post 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
Post Reply