Menu
I am trying to write a MySQL query that retrieves one record from table "projects" that has a one-to-many relationship with table "tags". My application uses 4 tables to do this:
Projects - the projects table
Entities - entity table; references several application resources
Tags - tags table
Tag_entity - links tags to entities
Is it possible to write the query in such a way that multiple values from table "Tags" are concatenated into one result column? I'd prefer doing this without using subqueries.
Table clarification:
-------------
| Tag_Entity |
------------- ---------- | ----------- | -------
| Projects | | Entities | | - id | | Tags |
| ----------- | | -------- | | - tag_id | | ----- |
| - id | --> | - id | --> | - entity_id | --> | id |
| - entity_id | ---------- ------------- | name |
------------- -------
Desired result:
Projects.id Entities.id Tags.name (concatenated)
1 5 'foo','bar','etc'
281
Concatenate Rows Using COALESCE All you have to do is, declare a varchar variable and inside the coalesce, concat the variable with comma and the column, then assign the COALESCE to the variable. In this method, you don't need to worry about the trailing comma.
The GROUP_CONCAT() function in MySQL is used to concatenate data from multiple rows into one field. This is an aggregate (GROUP BY) function which returns a String value, if the group contains at least one non-NULL value.
see GROUP_CONCAT
example:
mysql> SELECT * FROM blah;
+----+-----+-----------+
| K | grp | name |
+----+-----+-----------+
| 1 | 1 | foo |
| 2 | 1 | bar |
| 3 | 2 | hydrogen |
| 4 | 4 | dasher |
| 5 | 2 | helium |
| 6 | 2 | lithium |
| 7 | 4 | dancer |
| 8 | 3 | winken |
| 9 | 4 | prancer |
| 10 | 2 | beryllium |
| 11 | 1 | baz |
| 12 | 3 | blinken |
| 13 | 4 | vixen |
| 14 | 1 | quux |
| 15 | 4 | comet |
| 16 | 2 | boron |
| 17 | 4 | cupid |
| 18 | 4 | donner |
| 19 | 4 | blitzen |
| 20 | 3 | nod |
| 21 | 4 | rudolph |
+----+-----+-----------+
21 rows in set (0.00 sec)
mysql> SELECT grp, GROUP_CONCAT(name ORDER BY K) FROM blah GROUP BY grp;
+-----+----------------------------------------------------------------+
| grp | GROUP_CONCAT(name ORDER BY K) |
+-----+----------------------------------------------------------------+
| 1 | foo,bar,baz,quux |
| 2 | hydrogen,helium,lithium,beryllium,boron |
| 3 | winken,blinken,nod |
| 4 | dasher,dancer,prancer,vixen,comet,cupid,donner,blitzen,rudolph |
+-----+----------------------------------------------------------------+
4 rows in set (0.00 sec)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
