Codeigniter 3 GROUP_CONCAT and Join

Question

我正在尝试连接codeigniter 3中的一些表，我使用group_concat进行了搜索，但没有找到任何成功的结果。

这是我的桌子：

产品表

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

场合表：

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

门店位置表：

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

关系产品_城市

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

关系产品_引用

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

有可能这样算出我的预期结果吗？

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

谢谢你的帮助。

Answer 1

使用：

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;

结果：

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

演示