List:General Discussion« Previous MessageNext Message »
From:Moon's Father Date:February 4 2008 2:59am
Subject:Re: Can these two queries be combined into one?
View as plain text  
I think it doesn't need to combine these two query with one.

On Jan 31, 2008 9:45 AM, <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




-- 
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn

Thread
Can these two queries be combined into one?webtek2001-mysql31 Jan
  • Re: Can these two queries be combined into one?Peter Brawley31 Jan
  • Re: Can these two queries be combined into one?Moon's Father4 Feb