MySQL - SELECT all references from an ID in a SET field

user_6635203 0
Viewed 37

I dont know much about MySQL but i bet this is possible somehow.

I have these two sample table: 

TableA that contains (ID, name)
TableB that contains (TableA_ID, TableC_ID)

I'm trying to make a view out of the tableA that also lists in a SET field all the TableC_ID through the TableA_ID reference to TableA.ID. The output is meant to be something like:

+------------+------------+------------------------+
| ID         | name       | set(TableB.TableC_ID)  |
+------------+------------+------------------------+
|          1 |      maria |        (1, 3, 4, 6, 8) |
+------------+------------+------------------------+

Hopefully I was clear enough, thank you for the attention.

mysql
1 Answers

could be you are looking for a group_concat 

   SELECT 
       A.id,
       A.name,
       GROUP_CONCAT(B.TableC_ID)
   FROM TableA AS A
   INNER JOIN TableB AS B ON A.id = B.TableA_ID
   GROUP BY A.id

as 

   SELECT A.id, A.name, CONCAT('(',GROUP_CONCAT(B.TableC_ID) , ')') `SET(TableB.TableC_ID)`
   FROM TableA AS A 
   INNER JOIN TableB AS B on A.id = B.TableA_ID 
   GROUP BY A.id
user_35223120
Related