Hello, Lana.
Both queries works fine in 4.1.7 version. There were a lot of bugs in earlier versions of MySQL with subqueries. I think you should upgrade to the latest release. Did you use $sql=".." in mysql client program? That won't work. Try just SELECT * from user_info WHERE (login_name, user_passwd) IN ( SELECT login_name, user_passwd FROM new_user_info); If you want to set variable use SET @variable='....' syntax. "L a n a" <[EMAIL PROTECTED]> wrote: > 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 > > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
