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
[EMAIL PROTECTED] 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
[EMAIL PROTECTED] 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
