Sorry, mysql doesn't support sub queries until version 4.1
-Eric
Scott Purcell wrote:
Hello, I am accustomed to Oracle query syntax, and I am having trouble with this following query which uses the IN. I am running mysql ( Ver 12.21 Distrib 4.0.15, for Win95/Win98 (i32) ) which does not support the IN.
How do I rewrite the below query to function?
Basically what I have is three tables (see below) I want to only show the category (name and id) where there is a quantity of greater than 0 in the item table where the item.id is referenced in the item_cat_rel.id and the item_cat_rel.cat_id = category_id.
QUERY__
select c.cat_id, c.name, crel.id, crel.cat_id
from category c, item_cat_rel crel
where c.parent_id = 0 and c.visible = 'Y'
and c.id = crel.cat_id and crel.id IN (select id from item where quantity > 1)
order by c.sort
I tried replacing IN with = but it does not work.
Any help would be appreciated. Thanks, Scott
mysql> describe category; +-----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+----------------+ | cat_id | int(11) | | PRI | NULL | auto_increment | | parent_id | int(11) | YES | | NULL | | | visible | char(1) | | | Y | | | sort | int(11) | YES | | NULL | | | name | varchar(200) | YES | | NULL | | +-----------+--------------+------+-----+---------+----------------+ 5 rows in set (0.03 sec)
mysql> mysql> describe item_cat_rel -> ; +--------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------+------+-----+---------+-------+ | id | int(11) | YES | MUL | NULL | | | cat_id | int(11) | | | 0 | | +--------+---------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
mysql> describe item; +-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | manufacturer_id | varchar(50) | YES | | NULL | | | name | varchar(255) | YES | | NULL | | | description | varchar(255) | YES | | NULL | | | short_desc | varchar(255) | YES | | NULL | | | asset_id | varchar(14) | YES | | NULL | | | dimensions | varchar(50) | YES | | NULL | | | pounds | int(11) | YES | | NULL | | | price | decimal(9,2) | YES | | NULL | | | sale_price | decimal(9,2) | YES | | NULL | | | quantity | int(11) | | | 0 | | | featured | char(1) | | | N | | | seasonal | char(1) | | | N | | +-----------------+--------------+------+-----+---------+----------------+ 13 rows in set (0.02 sec)
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
