List:General Discussion« Previous MessageNext Message »
From:<webtek2001-mysql Date:January 31 2008 1:45am
Subject:Can these two queries be combined into one?
View as plain text  
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
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