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.
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