"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

Reply via email to