concatenate several fields into one with SQL

Question

i have three tables tag, page, pagetag

With the next data

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?

Answer 1

Yep, you can do it across the 3 something like the below:

SELECT page_tag.id, page.name, group_concat(tags.name) FROM tag,page, page_tag
where page_tag.page_id = page.page_id AND page_tag.tag_id = tag.id;

Has not been tested, and could be probably be written a tad more efficiently, but should get you started!

Also, MySQL is assumed, so may not play so nice with MSSQL! And MySQL isn't wild about hyphens in field names, so changed to underscores in the above examples.

Answer 2

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

Answer 3

I think you may need to use multiple updates.

Something like (not tested):

select ID as 'PageId', Name as 'PageName', null as 'Tags'
into #temp 
from [PageTable]

declare @lastOp int
set @lastOp = 1

while @lastOp > 0
begin
    update p
    set p.tags = isnull(tags + ', ', '' ) + t.[Tagid]
    from #temp p
        inner join [TagTable] t
            on p.[PageId] = t.[PageId]
    where p.tags not like '%' + t.[Tagid] + '%'

    set  @lastOp == @@rowcount
end

select * from #temp

Ugly though.

That example's T-SQL, but I think MySql has equivalents to everything used.

Answer 4

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;

Answer 5

@PConroy's answer is right for MySQL, but @Matthew is almost right -- there is no standard way in SQL to "de-normalising" the data into a single field.

Answer 6

As PConroy mentioned group_concat seems to do the work for mysql. A query such as:

SELECT page.id as id, page.name as name, group_concat(distinct tag.name) as tags FROM tag,page, pagetag
where pageid=page.id AND tagid = tag.id group by page.id

returns

id   name    tags
---------------------------
1    page 1  tag2,tag3,tag1
2    page 2  tag1,tag3
3    page 3  tag4

However, I am not getting the pages without tags. I guess i need to write my query with left outer joins. Can anyone help me? I am kind of newby with joins. Thanks in advanced.

Answer 7

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.

Answer 8

I got a solution playing with joins. The query is:

SELECT page.id AS id, page.name AS name, tagstable.tags AS tags FROM page 
LEFT OUTER JOIN 
(SELECT pagetag.pageid, GROUP_CONCAT(distinct tag.name) as tags FROM tag INNER JOIN pagetag ON tagid=tag.id GROUP BY pagetag.pageid) AS tagstable ON tagstable.pageid=page.id GROUP BY page.id

And this will be the output:

id   name    tags
---------------------------
1    page 1  tag2,tag3,tag1
2    page 2  tag1,tag3
3    page 3  tag4
4    page 4  NULL

is it possible to boost the query speed writing it another way?

Answer 9

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.

You can use the COALESCE function to remove the Nulls if you need to:

select COALESCE(pagetag.id, '') AS id ...

It will return the first non-null value from it's list of parameters.