mysql select from one table with concat results from another table
mysql, tags
Solution
Group Concat:
SELECT
docs.id AS id,
docs.orig_file AS orig_file,
docs.date_sent AS date_sent,
GROUP_CONCAT(distinct tags.tag_name) AS alltags
FROM documat AS docs
LEFT JOIN documat_file2tag AS f2t ON f2t.doc_id = docs.id
LEFT JOIN documat_tags AS tags ON tags.id = f2t.tag_id
GROUP BY docs.id
Problem
So I need to select distinct values from one table, but join all the related values from another table but within the same query. Basically I'm following the Toxi TagSystem Schema http://forge.mysql.com/wiki/TagSchema#Toxi Three-table many-to-many mapping between. And I need to show all the inserted values (docs) one on each row, but one of the columns I would like to have all the tags that the file has separated by a comma for example. For now I have ``` SELECT docs.id AS id, docs.orig_file AS orig_file, docs.date_sent AS date_sent, tags.tag_name AS alltags FROM documat AS docs LEFT JOIN documat_file2tag AS f2t ON f2t.doc_id = docs.id LEFT JOIN documat_tags AS tags ON tags.id = f2t.tag_id ``` But this is repeating the rows if a specific docs.id has more than a tag. The final result I would like to have on each row is: ``` | ID | orig_file | date_sent | alltags | ``` With the desired result example: ``` | X | example_value.pdf | 2012-03-23 10:14:05 | tag_ex_1, tag_ex_2, etc | ```