I have an application that uses a nested set model class to organise my data, however I'm trying to write a query that will group_concat my results. I know I need to put some sub select statements somewhere but I can't figure it out!
Here's my structure at the moment:
table: person
-----------+------------+-----------
|Person_ID | Name | Age |
-----------+------------+-----------
| 1 | Mark Vance | 19 |
| 2 | Michael Tsu| 22 |
| 3 | Mark Jones | 29 |
| 4 | Sara Young | 25 |
-----------+------------+-----------
table: person_to_group
----+------------+-----------
|ID | Person_ID | Group_ID |
----+------------+-----------
| 1 | 3 | 1 |
| 2 | 3 | 2 |
| 3 | 1 | 2 |
| 4 | 4 | 3 |
----+------------+-----------
table: groups
----------+--------------+--------------+-------------
|Group_ID | Group_Name | Group_Left | Group_Right |
----------+--------------+--------------+-------------
| 1 | Root | 1 | 6 |
| 2 | Node | 2 | 5 |
| 3 | Sub Node | 3 | 4 |
----------+--------------+--------------+-------------
I need to render something like this with my results:
//Grab the group_IDs for this person and put them in the class tag...
<li class="2 3">Sara Young is in the Sub Node Group</li>
Notice that although Sara is in the Sub Node group, she is still being given the id for Node aswell because she is a child of Node.
The following is the query that I am working with as a starting point.
SELECT *, GROUP_CONCAT( CAST( gg.Group_ID AS CHAR ) SEPARATOR ' ' ) Group_IDs
FROM groups gg
LEFT JOIN person_to_group AS t1 ON gg.Group_ID = t1.Group_ID
LEFT JOIN person AS t2 ON t2.Person_ID = t1.Person_ID
GROUP BY t2.per_ID
ORDER BY t2.Name ASC
Any help would be much appreciated!