Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Concatenate several fields into one with SQL

Tags:

sql

mysql

I have three tables tag, page, pagetag

With the data below

page

ID      NAME
1       page 1
2       page 2
3       page 3
4       page 4

tag

ID      NAME
1       tag 1
2       tag 2
3       tag 3
4       tag 4

pagetag

ID   PAGEID  TAGID
1    2        1
2    2        3
3    3        4
4    1        1
5    1        2
6    1        3

I would like to get a string containing the correspondent tag names for each page with SQL in a single query. This is my desired output.

ID      NAME       TAGS
1       page 1     tag 1, tag 2, tag 3
2       page 2     tag 1, tag 3
3       page 3     tag 4
4       page 4    

Is this possible with SQL?


I am using MySQL. Nonetheless, I would like a database vendor independent solution if possible.

like image 637
Sergio del Amo Avatar asked Sep 01 '08 10:09

Sergio del Amo


2 Answers

Sergio del Amo:

However, I am not getting the pages without tags. I guess i need to write my query with left outer joins.

SELECT pagetag.id, page.name, group_concat(tag.name)
FROM
(
    page LEFT JOIN pagetag ON page.id = pagetag.pageid
)
LEFT JOIN tag ON pagetag.tagid = tag.id
GROUP BY page.id;

Not a very pretty query, but should give you what you want - pagetag.id and group_concat(tag.name) will be null for page 4 in the example you've posted above, but the page shall appear in the results.

like image 67
ConroyP Avatar answered Sep 16 '22 22:09

ConroyP


As far as I'm aware SQL92 doesn't define how string concatenation should be done. This means that most engines have their own method.

If you want a database independent method, you'll have to do it outside of the database.

(untested in all but Oracle)

Oracle

SELECT field1 | ', ' | field2
FROM table;

MS SQL

SELECT field1 + ', ' + field2
FROM table;

MySQL

SELECT concat(field1,', ',field2)
FROM table;

PostgeSQL

SELECT field1 || ', ' || field2
FROM table;
like image 23
Matthew Watson Avatar answered Sep 18 '22 22:09

Matthew Watson