"Scott Purcell" <[EMAIL PROTECTED]> wrote on 05/13/2005 12:05:05 PM:
> 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) > You are already INNER JOINing the item_cat_rel table, just add your item table to the join list, too. Here is a link to an article that may help you to convert your subquery (the IN was not your problem) into a JOIN: http://dev.mysql.com/doc/mysql/en/rewriting-subqueries.html This is how I would rewrite your query: SELECT c.cat_id , c.name , crel.id , crel.cat_id FROM category c INNER JOIN item_cat_rel crel ON c.id = crel.cat_id INNER JOIN item i ON crel.id = i.id WHERE c.parent_id = 0 and c.visible = 'Y' and i.quantity > 1 ORDER BY c.sort <side note> I put the test of i.quantity in the WHERE clause because I think it is unlikely that you have an index on "item" that covers both id and quantity so adding that term to the ON clause would probably not have helped. This is an alternative way to write the same query. This one MAY perform better than the other but not because of index selection. (It would be due to possibly minimizing the # of records JOINed from the item table before the WHERE clause is evaluated). SELECT c.cat_id , c.name , crel.id , crel.cat_id FROM category c INNER JOIN item_cat_rel crel ON c.id = crel.cat_id INNER JOIN item i ON crel.id = i.id and i.quantity > 1 WHERE c.parent_id = 0 and c.visible = 'Y' ORDER BY c.sort </side note> Just so you know (and because it is my pet peeve on this list), you are no longer forced to use the comma-separated list as a means of JOINing tables. Now that you are using MySQL, you can start using the ANSI-compliant, explicitly declared JOIN clauses ( http://dev.mysql.com/doc/mysql/en/join.html). The implicit join list is still supported but my preference is to explicity declare the JOINs and their types. It's just my thing (and it's the only way to declare a LEFT or RIGHT JOINs with MySQL). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
