[SOLVED] MySQL subquery problem

[avvllvva]

Hi all,

 

Please look at following details,

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Table structure

1.tbl_product (stores product details)

  product_id, title

2.tbl_category (stores category details)

  category_id, title

3.tbl_map_product_category (stores product-category mapping. Here sometimes there would be multiple entry of same product['product_id']. ie; one product may have multiple categories )

  product_id, category_id

 

Hope following example will give u a clear picture,

 

Example

tbl_product

id title

1  product_1

2  product_2 

 

tbl_category

id title

1  category_1

2  category_2

3  category_3

4  category_4

 

tbl_map_product_category

product_id Category_id

1     2

2     1

2     2

2     4

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

 

Now I can explain my requirement,

 

I want to get all categories from tbl_category, but each category_id should be check with tbl_map_product_category table  against a particular product.(it doesn't means getting only the categories for that particular product, but I needs to identify these categories of that particular product along with complete categories).

 

eg:- For product_id - 2

    I needs to display all categories (category 1,2,3,4), among these categories I need to identify the categories 1,2,4 (which are belongs to product_id 2 )

 

How can do it in a single mysql query ?

 

This is the query i've tried - SELECT c.*, (SELECT category_id FROM tbl_map_product_category

                                          WHERE  product_id='var_particular_product_id') as category_map_id

                  FROM tbl_category c

                             

From this query result, I will get the complete categories and I can easily identify the categories against a particular product by simply    comparing c.id = category_map_id

 

                             

                              case 1 : for product_id - 1 (replacing var_particular_product_id with 1 in the query )

                                        This is working fine. It produces the complete category list and I can able to idetify the category belongs to product_id 1.

                                       

                              case 2 : for product_id - 2 (replacing var_particular_product_id with 2 in the query )

                                        This giving empty result. Because the sub query returns multiple rows (since product_id -> 2 have three categories)

 

So for case 2, how can I modify my query to getting the exact result?

 

Please help me..... if u need more explanation, plz let me know....

 

thanks     

[kickstart]

Hi

 

Think I would try something like this:-

 

SELECT *
FROM tbl_category a
LEFT OUTER JOIN (SELECT Category_id FROM tbl_map_product_category z INNER JOIN tbl_product y ON z.Product_id = y.id WHERE y.title = 'Some product title') b ON a.id = b.Category_id
LEFT OUTER JOIN tbl_product c ON b.Product_id = c.id

 

This will give you a listing of all categories with an extra column for the category id, which if null means the product wasn't in that category. This is searching on a product name.

 

If you have the product id then no need to even use the product table

 

SELECT *
FROM tbl_category a
LEFT OUTER JOIN tbl_map_product_category b 
ON a.id = b.Category_id AND b.Product_id = 1

 

All the best

 

Keith

[avvllvva]

If you have the product id then no need to even use the product table

 

SELECT *
FROM tbl_category a
LEFT OUTER JOIN tbl_map_product_category b 
ON a.id = b.Category_id AND b.Product_id = 1

 

 

Yes I have the product id,

and your query is working perfectly.

 

Thank you very much kickstart.

 

I've also posted a topic in php forum(http://www.phpfreaks.com/forums/index.php/topic,271418.0.html), the practical use of this.