From: Peter Brawley
Date: January 31 2008 6:02am
Subject: Re: Can these two queries be combined into one?
List-Archive: http://lists.mysql.com/mysql/211157
Message-Id: <47A16473.5070907@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

> Is there anyway to combine these 2 queries into 1?

Is this what you mean?

-- list all prods-cats
SELECT p.prod_id, c.category
FROM product_table p
LEFT JOIN catproduct_table cp ON cp.product_id = p.product_id
JOIN product_table p ON p.product_id = cp.product_id;

... or this ...

-- group cats for prods
SELECT p.prod_id, GROUP_CONCAT(c.category)
FROM product_table p
LEFT JOIN catproduct_table cp ON cp.product_id = p.product_id
JOIN product_table p ON p.product_id = cp.product_id
GROUP BY p.prod_id ;

PB

webtek2001-mysql@stripped wrote:

Hi,

  I'm currently using 2 queries to obtain some data and would like to know if I can combine these two queries into one query.

I have a database with 3 tables:

1.  A category table
2.  A product table
3.  A normalized "catproduct" table that is used to determine which product is in which category.  A product may be in multiple categories.

I have a website where when a person navigates to a category it shows all the products.  I would like to create a "related categories" list by finding all the categories the products in the current category belong to.

Right now I use 2 SQL statements:

1. The first query gets all the products in the current category.

SELECT product_table.prod_id FROM
        product_table
     LEFT JOIN catproduct_table ON (catproduct_table.product_id = product_table.product_id)
     WHERE (catproduct_table.category_id = '1') LIMIT 5

2. The second query gets all the categories that these products belong to:

SELECT category_table.category FROM category_table
   LEFT JOIN catproduct_table ON (catproduct_table.category_id = category_table.category_id)
   LEFT JOIN product_table ON (product_table.product_id = catproduct_table.product_id)
   WHERE product_table.prod_id IN ('1000,1001,1002,1003,1004')


These two queries are my proof of concept so I'm only limiting the first query to 5 results.  In reality I have thousands of products per category so I prefer not to limit the first query.  I don't have many categories so the results of the second query are quite small.

Is there anyway to combine these 2 queries into 1?

Thanks,
Mason

http://www.retailretreat.com
  



webtek2001-mysql@stripped wrote:
> Hi,
>
>   I'm currently using 2 queries to obtain some data and would like to know if I can combine these two queries into one query.
>
> I have a database with 3 tables:
>
> 1.  A category table
> 2.  A product table
> 3.  A normalized "catproduct" table that is used to determine which product is in which category.  A product may be in multiple categories.
>
> I have a website where when a person navigates to a category it shows all the products.  I would like to create a "related categories" list by finding all the categories the products in the current category belong to.
>
> Right now I use 2 SQL statements:
>
> 1. The first query gets all the products in the current category.
>
> SELECT product_table.prod_id FROM
>         product_table
>      LEFT JOIN catproduct_table ON (catproduct_table.product_id = product_table.product_id)
>      WHERE (catproduct_table.category_id = '1') LIMIT 5
>
> 2. The second query gets all the categories that these products belong to:
>
> SELECT category_table.category FROM category_table
>    LEFT JOIN catproduct_table ON (catproduct_table.category_id = category_table.category_id)
>    LEFT JOIN product_table ON (product_table.product_id = catproduct_table.product_id)
>    WHERE product_table.prod_id IN ('1000,1001,1002,1003,1004')
>
>
> These two queries are my proof of concept so I'm only limiting the first query to 5 results.  In reality I have thousands of products per category so I prefer not to limit the first query.  I don't have many categories so the results of the second query are quite small.
>
> Is there anyway to combine these 2 queries into 1?
>
> Thanks,
> Mason
>
> http://www.retailretreat.com
>   
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition. 
> Version: 7.5.516 / Virus Database: 269.19.16/1251 - Release Date: 1/30/2008 9:29 AM
>