Codeigniter 3 GROUP_CONCAT and Join-CodePudding

I'm trying to join some tables in codeigniter 3, I have searched by using group_concat, but I didn't find any success.

Here's my tables:

Product Table

   id     |   flower   |       
_____________________________
   1      |    Rose    | 
   2      |    Tulip   |

Occasion table :

   id     |   occasion_name   |       
_____________________________
   1      |    Valentine      | 
   2      |    Mother's Day   | 
   3      |    Birthday       |

Store Location table :

   id     |   city_name   |       
_____________________________
   1      |    London      | 
   2      |    Belfast     | 
   3      |    Bristol     |

Relational product_city

   id     | product_id | product_city     
_______________________________________
   1      |    1       |    1       | 
   2      |    1       |    2       | 
   3      |    2       |    3       |
   4      |    2       |    2       |
   5      |    2       |    1       |

Relational product_occasion

   id     | product_id | product_occasion     
_______________________________________
   1      |    1       |    3       | 
   2      |    1       |    1       | 
   3      |    2       |    1       |
   4      |    2       |    3       |
   5      |    2       |    2       |

Is it possible to figure out my expected result like this?

   id     | flower      |           Occasion                          |    City
_____________________________________________________________________________________
   1      |    Rose     |    3-Birthday,1-Valentine                   | 1-London,2-Belfast
   2      |    Tulip    |    1-Valentine,3-Birthday,2-Mother's Day    | 3-Bristol,2-Belfast,1-London

Thank you for your help.

CodePudding user response：

Use:

select id,flower,group_concat(DISTINCT  Occasion) as Occasion ,group_concat(DISTINCT  City) as City
from (
select p.id,p.flower,(concat(o.id,'-',o.occasion_name)) as Occasion ,(concat(sl.id,'-',sl.city_name))  as City 
from Product p 
inner join product_city pc on p.id=pc.product_id 
inner join Store_Location sl   on   sl.id=pc.product_city 
inner join product_occasion po on po.product_id=p.id
inner join Occasion o on o.id=po.product_occasion
group by p.id,p.flower,Occasion,City ) as t1
group by id,flower;

Result:

id  flower       Occasion                              City
 1   Rose    1-Valentine,3-Birthday                 1-London,2-Belfast
 2   Tulip   1-Valentine,2-Mother's Day,3-Birthday  1-London,2-Belfast,3-Bristol

Demo