mysql join on product category multiple relation query

0
votes

I have three tables as below, each product may belong to multiple categories.

PRODUCT TABLE AS P
1. ID
2. NAME

CATEGORY TABLE AS C
1. ID 2. NAME

RELATION TABLE AS R 1. ID
2. P_ID
3. C_ID

Now I want to get a list of all products in product table, and with their belonging category name display as well.

How do I write this query?

I can get the category ids pulled from the same query, but don't know how to get all the name pulled as well. Here's what I tried.

select p.*,y.* 
  from p 
  left join (select p_id,group_concat(c_id) as category_ids 
               from relation group by p_id) as y on p.id=y.p_id
2
mysqljoin
Just to mention...you generally don't want to cram all (ids, names, ...) together in a string like that, particularly if your intent is to parse it later. Among other issues, now you can't reliably have a category name with a comma in it. – cHao
thanks for reminding me this, i will use a separator instead of a comma. – Tyler Tong
Most other separators you might pick would open you up to the same issue. If you insist on concatenating stuff together in one field (which is just asking for trouble, IMO), your separator character needs to be one that can never reasonably appear in a category id/name (I might suggest a newline, for example), and you need to take steps to ensure it never does. – cHao

2 Answers

0
votes

Do both JOIN operations (to the relation table, and from there to the table containing the category names) and feed the result to your aggregation function (GROUP_CONCAT)

SELECT P.Name, GROUP_CONCAT(DISTINCT C.Name ORDER BY C.Name SEPARATOR '|') categories
  FROM Product P
  LEFT JOIN Relation R ON P.ID = R.P_ID
  LEFT JOIN Category C ON R.C_ID = C.ID
 GROUP BY P.ID, P.Name

This will give you one row per product with the categories separated by |.

This uses LEFT JOIN operations so it won't suppress products that have no categories.

0
votes
Select P.Name, C.Name
  From RELATION R Inner Join PRODUCT P
  On R.P_ID=P.Id
  Inner Join Category C
  On C.Id=R.C_ID

This query will get you all the products, with their corresponding category.

I want to give you a small explanation about the difference between Inner Join and Left Join.
If we take as an example 2 tables :
TA(idA, description) and TB(idB, idA, description). 

Select TA.description, TB.description
From TA Inner Join TB On TA.IdA = TB.IdA

will get only the rows in TA, that have a corresponding one in TB.
On the other side, 

Select TA.description, TB.description
From TA Left Join TB On TA.IdA = TB.IdA

will get all the rows of TA and if the row in TA doesn't have a corresponding one in TB, TB.description for this row will be NULL.

Hope this helps!