5

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.

Improve this question

6 Answers 6

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

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.

Improve this answer

Comments

1

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;
Improve this answer

1 Comment

The SQL standard defines || as the string concatenation operator.
1

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?

Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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.

Improve this answer

1 Comment

@Nhan don't edit answers just to change the formatting to match your opinions.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.