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]



Reply via email to