CODEWITHSUNDEEP
mysql
hellsgate
hellsgate

Reputation: 6005

How do I concatenate string values from a lookup table in MYSQL

I have three tables: upload, tag and upload_tag. Upload holds details of a file upload, tag holds tag names and upload_tag is a lookup table which matches upload ids to tag ids i.e:

+--------------------------------------------------------+
| upload                                                 |
+--------------------------------------------------------+
|id         |name                                        |
+-----------+--------------------------------------------+
|1          |file_1.jpg                                  |
+-----------+--------------------------------------------+
|2          |file_2.doc                                  |
+-----------+--------------------------------------------+

+--------------------------------------------------------+
|tag                                                     |
+--------------------------------------------------------+
|id         |name                                        |
+-----------+--------------------------------------------+
|1          |a_tag                                       |
+-----------+--------------------------------------------+
|2          |b_tag                                       |
+-----------+--------------------------------------------+
|3          |c_tag                                       |
+-----------+--------------------------------------------+

+--------------------------------------------------------+
|upload_tag                                              |
+-----------+--------------------------------------------+
|upload_id  |tag_id                                      |
+-----------+--------------------------------------------+
|1          |1                                           |
+-----------+--------------------------------------------+
|1          |2                                           |
+-----------+--------------------------------------------+
|2          |3                                           |
+-----------+--------------------------------------------+
|2          |2                                           |
+-----------+--------------------------------------------+

I need to pull uploads from the database with the string of tags, but all in one row so the result would look like:

+----------------------------------------------------+
|filename      |tags                                 |
+--------------+-------------------------------------+
|file_1.jpg    |a_tag; b_tag                         |
+--------------+-------------------------------------+
|file_2.doc    |c_tag; b_tag                         |
+--------------+-------------------------------------+

At the moment I'm thinking that the best way to do this would be to create a MySQL function which takes an upload id and returns the formatted string of tags. Am I correct here, or is there a better way to achieve this?

Upvotes: 0

Views: 690

Answers (2)

Sashi Kant
Sashi Kant

Reputation: 13465

Try this ::

select 
upload.name as filename
GROUP_CONCAT(tag.name SEPARATOR '; ') as tags
from 
upload_tag ut 
inner join upload  on (ut.upload_id = upload.id)
inner join tags on (ut.tag_id = tags.id) 
group by ut.upload_id

Upvotes: 1

Mihai Stancu
Mihai Stancu

Reputation: 16127

You should use a LEFT JOIN clause and the GROUP_CONCAT() function.

SELECT          u.name AS filename,
                GROUP_CONCAT(t.name SEPARATOR '; ') AS tags
    FROM        upload u
    LEFT JOIN   upload_tags ut
        ON      u.id = ut.upload_id
    LEFT JOIN   tag t
        ON      t.id = ut.tag_id
    GROUP BY    u.id;

GROUP_CONCAT is an aggregate function, meaning it works on multiple rows (organized as groups) as opposed to CONCAT which works on multiple columns.

Upvotes: 2

Related Questions