Tuesday, August 23, 2011

Many-to-many MySql relation: how to retrieve all related entries in one query?

Suppose there are 3 Table 1) Products , 2)Category & 3)Pro_Cat :- Many to Many RelationShip B/w Products & Category Table :-

1) Products Table :-                                  


Product_Id
Product_Name
1
Samsung
2
Relience
3
Micromax

2) Category Table :-


Category_Id
Category_Name
1
Mobile
2
Retails
3
Usb

3) Product_Category Many into Many Relationship Table :-


Product_Id
Category_Id
1
1
2
1
2
2
3
1
3
3


Result Query :-

SELECT
  product.product_name,
  GROUP_CONCAT(category.category_name) AS category_name
FROM product,category,pro_cat
Where product.product_id = pro_cat.product_id
and category.category_id = pro_cat.category_id
GROUP BY product.product_id;

Result :-


Product_Name
Category_Names
Samsung
Mobile
Relience
Mobile,Retails
Micromax
Mobile ,Usb

No comments: