help with SQL syntax error

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?

bastien

mysql does not support subselects yet...

break this into two queries where the first gets the subselect query data and then manipulate this result to get the proper IN formatted data

Sxooter

OR switch to either the latest MySQL, or go to FirebirdSQL or PostgreSQL. All of those support subselects, although I'm not sure how fast the MySQL planner is just yet on complex sub queries and such.

Lars_Berg

SELECT COUNT(*) FROM individual_tbl WHERE Last_name LIKE '%na%'

The subselect is meaningless since it does not restrict anything further. You have the same condition in the where clauses.

This

ID IN (SELECT ID, Last_name

is not allowed. I.e. comparing ID to two columns.

davidklonski

this is not entirely correct
the initial query asked for all the last names which start with 'n'.
The second query restricts it further to all the last names with 'a' after the 'n'.
their combination should provide all the last names with begin with 'na'.

I know that there is an easier way to obtain this information, but this is the way I need to get that info.

You are correct in your second remark

Lars_Berg

Yes, I missed that. You still don't need a subquery.

To get a count of all name starting with na

SELECT COUNT(*) FROM individual_tbl WHERE Last_name LIKE 'na%'

Your subquery would have retrieved records where name started with N and containg na which is not the same.

Sxooter

Can't this just be done as:

select count(*) from individual_tbl where last_name like 'N%na'

Or is id not a unique field in this table?

davidklonski

I allow users to perform search on search results.
This theoretically can be done indenfinitely.
To make things simple, I use subqueries

Sxooter

I don't see it being any easier with sub selects than it is to add ANDED conditions to the where statement.

davidklonski

subqueries allow you to easily support undo and redo of queries.
Just strip the wrapping query.
Otherwise I need to know what conditions were added in the last query and then strip them away

Sxooter

But assumes you're passing queries about in the client side and back to your application, which is a security hazard.

Better to just pass the terms your searching on and then you can lop off the ones on the end just as easily.

Plus, subqueries are usually slower than ANDing terms in the where clause.

davidklonski

I am not sending any queries to the client side at all.
I agree that subqueries are slower.
I will give it further thought