3

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 |
eduardev
  • 473
  • 8
  • 26
  • 1
    Can you use subselect and GROUP_CONCAT? See http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field – ovolko Apr 18 '12 at 08:37
  • @ovolko because I have three table I need to subselect right. How? Sql is really my weak point :P – eduardev Apr 18 '12 at 08:40
  • ok seems like subselect is not even required :) – ovolko Apr 18 '12 at 08:43

1 Answers1

7

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
John Green
  • 13,241
  • 3
  • 29
  • 51