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]
