Re: IN giving me a fit
Scott Purcell wrote: I am in the docs trying to use the IN (13.1.8.3. Subqueries with ANY, IN, and SOME). Version: mysql Ver12.21 distrib 4.0.15 Win95/Win98(i32) Look at http://dev.mysql.com/doc/mysql/en/subqueries.html Subqueries aren't supported until 4.1. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: IN giving me a fit
Hi, This is just what you're looking for : select item.id, item.name from item,item_cat_rel where item.id =item_cat_rel.id and item_cat_rel.cat_id = 5; +--+--+ | id | name | +--+--+ | 5000 | Triple Cage Hook | +--+--+ 1 row in set (0.01 sec) Joisn are better than subqueries. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Scott Purcell [mailto:[EMAIL PROTECTED] Sent: vendredi 29 avril 2005 20:14 To: mysql@lists.mysql.com Subject: IN giving me a fit Hello, I am in the docs trying to use the IN (13.1.8.3. Subqueries with ANY, IN, and SOME). Version: mysql Ver12.21 distrib 4.0.15 Win95/Win98(i32) I have two tables that are InnoDB types and I am trying to do simple IN but it argues with my syntax. mysql select id, name - from item where id IN (select id from item_cat_rel where cat_id = 5); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp onds to your MySQL server version for the right syntax to use near 'select id fr om item_cat_rel where cat_id = 5)' at line 2 mysql Why? Here are the tables. CREATE TABLE ITEM ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, manufacturer_id varchar(50), name varchar(255), ) TYPE=InnoDB; insert into ITEM (id, manufacturer_id, name, description, short_desc, height, width, diameter, pounds, price, discount, quantity) values (5000, '9.90151', 'Triple Cage Hook', 'Solid wrought iron, is sculpted by hand into twisted cage hooks to hold your coats, bathrobes, towels and hats.', 'Triple Cage Hook', 9, 18.5, 4.5, 6, 35.00, 5, 1); CREATE TABLE ITEM_CAT_REL ( id INT, cat_id INT NOT NULL, key(id), FOREIGN KEY (id) references ITEM(id) on DELETE CASCADE ) TYPE=InnoDB; INSERT INTO ITEM_CAT_REL (id, cat_id) values (5000, 5); INSERT INTO ITEM_CAT_REL (id, cat_id) values (5000, 6); Scott K Purcell | Developer | VERTIS | 555 Washington Ave. 4th Floor | St. Louis, MO 63101 | 314.588.0720 Ext:1320 | [EMAIL PROTECTED] | http://www.vertisinc.com Vertis is the premier provider of targeted advertising, media, and marketing services that drive consumers to marketers more effectively. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IN giving me a fit
Your problem is that you are trying to do an 'IN' subquery in MySQL V4.0.x. Subqueries don't become available in MySQL until V4.1.x. You'll need to upgrade to V4.1 (or V5.0) to get that functionality. Rhino - Original Message - From: Scott Purcell [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, April 29, 2005 2:14 PM Subject: IN giving me a fit Hello, I am in the docs trying to use the IN (13.1.8.3. Subqueries with ANY, IN, and SOME). Version: mysql Ver12.21 distrib 4.0.15 Win95/Win98(i32) I have two tables that are InnoDB types and I am trying to do simple IN but it argues with my syntax. mysql select id, name - from item where id IN (select id from item_cat_rel where cat_id = 5); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp onds to your MySQL server version for the right syntax to use near 'select id fr om item_cat_rel where cat_id = 5)' at line 2 mysql Why? Here are the tables. CREATE TABLE ITEM ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, manufacturer_id varchar(50), name varchar(255), ) TYPE=InnoDB; insert into ITEM (id, manufacturer_id, name, description, short_desc, height, width, diameter, pounds, price, discount, quantity) values (5000, '9.90151', 'Triple Cage Hook', 'Solid wrought iron, is sculpted by hand into twisted cage hooks to hold your coats, bathrobes, towels and hats.', 'Triple Cage Hook', 9, 18.5, 4.5, 6, 35.00, 5, 1); CREATE TABLE ITEM_CAT_REL ( id INT, cat_id INT NOT NULL, key(id), FOREIGN KEY (id) references ITEM(id) on DELETE CASCADE ) TYPE=InnoDB; INSERT INTO ITEM_CAT_REL (id, cat_id) values (5000, 5); INSERT INTO ITEM_CAT_REL (id, cat_id) values (5000, 6); Scott K Purcell | Developer | VERTIS | 555 Washington Ave. 4th Floor | St. Louis, MO 63101 | 314.588.0720 Ext:1320 | [EMAIL PROTECTED] | http://www.vertisinc.com Vertis is the premier provider of targeted advertising, media, and marketing services that drive consumers to marketers more effectively. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.0 - Release Date: 29/04/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.0 - Release Date: 29/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
