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]

Reply via email to