Showing posts with label many to many mysql relation query. Show all posts
Showing posts with label many to many mysql relation query. Show all posts

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