I don't know if your desire to use the subquery form is academic or performance driven. If I were having this much trouble getting a subquery to work, I would refactor my queries using JOIN statements.
http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html (your query #1 below)This query will pull all of the information from the user_info table where the login_name matches a record from the new_user_info table SELECT u.* FROM user_info u INNER JOIN new_user_info nu ON nu.login_name = u.login_name (query #1 rev 2) Same as above but eliminating any duplicate rows (caused by multiple records in the new_user_info table sharing the same login) SELECT DISTINCT u.* FROM user_info u INNER JOIN new_user_info nu ON nu.login_name = u.login_name (your query #2 below)This query will pull all of the information from the user_info table where both the login_name and the password matches a record from the new_user_info table SELECT u.* FROM user_info u INNER JOIN new_user_info nu ON nu.login_name = u.login_name AND nu.user_passwd = u.user_passwd And to show some additional examples: This query will find all of the new_user_info records that don't already exist in the user_info table (as matched up by both login AND password) SELECT nu.* FROM new_user_info nu LEFT JOIN user_info u ON nu.login_name = u.login_name AND nu.user_passwd = u.user_passwd WHERE u.user_id IS NULL New users with the same login but different passwords as existing users (actually, this shows all matching logins and marks only the conflicts with an asterisk * ) SELECT if (u.user_passwd <> nu.user_passwd, '*','') as CONFLICT, nu.*, u.* FROM new_user_info nu INNER JOIN user_info u ON nu.login_name = u.login_name This is the same query but only for records whose logins match but passwords are different SELECT nu.*, u.* FROM new_user_info nu INNER JOIN user_info u ON nu.login_name = u.login_name WHERE u.user_passwd <> nu.user_passwd All users who share logins with at least 2 other users CREATE TEMP TABLE tmpList SELECT user_id, count(1) as freq FROM user_info GROUP BY user_id HAVING freq > 2; SELECT user_info.* FROM user_info INNER JOIN tmpList ON tmpList.user_id = user_info.user_id ORDER BY user_info.user_id; DROP TEMPORARY TABLE tmpList; Hope this helps you over your hurdle! :-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine "L a n a" <[EMAIL PROTECTED]> wrote on 11/11/2004 11:35:32 PM: > Hello, > Since I had a problem with except operator and subqueries, I investigated a > topic on mysql version capability. I tried a few examples which were offered > on this mailing list, all of them gave me a syntax error, so I've read a > manual and tried some examples from it. However, things that must work > still doesn't work I got frustrated... please help... > > I have the following two tables in mySQL 4.1.3-beta : > CREATE TABLE `user_info` ( > `comments` varchar(250) default '', > `user_id` int(11) NOT NULL auto_increment, > `login_name` varchar(20) default NULL, > `user_passwd` varchar(20) default NULL, > PRIMARY KEY (`user_id`), > KEY `user_id` (`user_id`) > ) TYPE=MyISAM; > > CREATE TABLE `new_user_info` ( > `comments` varchar(250) default '', > `user_id` int(11) NOT NULL auto_increment, > `login_name` varchar(20) default NULL, > `user_passwd` varchar(20) default NULL, > PRIMARY KEY (`user_id`), > KEY `user_id` (`user_id`) > ) TYPE=MyISAM; > > Basically two tables contain same structured info for old and new users. > > I've read the manual and there are two examples: > > 1. SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2); > 2. SELECT * FROM t1 WHERE (column1,column2) IN (SELECT column1,column2 FROM > t2); > > I adjusted them to my tables and tested as the following: > > 1.$sql = "SELECT * from user_info WHERE login_name = ANY ( SELECT login_name > from new_user_info)"; > 2.$sql = "SELECT * from user_info WHERE (login_name, user_passwd) IN ( > SELECT login_name, user_passwd FROM new_user_info)"; > > It gives the following error: > "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 'user_passwd) IN > ( SELECT login_name,user_passwd FROM new_user_i" > > Why? What's wrong? Can anyone help? > > Thank you, > Lana > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
