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