<p>I have three tables <code>tag</code>, <code>page</code>, <code>pagetag</code></p>

<p>With the data below</p>

<p><strong><em>page</em></strong></p>

<pre><code>ID      NAME
1       page 1
2       page 2
3       page 3
4       page 4
</code></pre>

<p><strong><em>tag</em></strong></p>

<pre><code>ID      NAME
1       tag 1
2       tag 2
3       tag 3
4       tag 4
</code></pre>

<p><strong><em>pagetag</em></strong></p>

<pre><code>ID   PAGEID  TAGID
1    2        1
2    2        3
3    3        4
4    1        1
5    1        2
6    1        3
</code></pre>

<p>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.</p>

<pre><code>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    
</code></pre>

<p>Is this possible with SQL?</p>

<hr>

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

[{'answer_id': 37700, 'author': 'ConroyP', 'author_id': 2287, 'author_profile': 'https://Stackoverflow.com/users/2287', 'pm_score': 2, 'selected': False, 'text': "<p>Yep, you can do it across the 3 something like the below:</p>\n\n<pre><code>SELECT page_tag.id, page.name, group_concat(tags.name)\nFROM tag, page, page_tag\nWHERE page_tag.page_id = page.page_id AND page_tag.tag_id = tag.id;\n</code></pre>\n\n<p>Has not been tested, and could be probably be written a tad more efficiently, but should get you started!</p>\n\n<p>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.</p>\n"}, {'answer_id': 37711, 'author': 'Keith', 'author_id': 905, 'author_profile': 'https://Stackoverflow.com/users/905', 'pm_score': 0, 'selected': False, 'text': "<p>I think you may need to use multiple updates.</p>\n\n<p>Something like (not tested):</p>\n\n<pre><code>select ID as 'PageId', Name as 'PageName', null as 'Tags'\ninto #temp \nfrom [PageTable]\n\ndeclare @lastOp int\nset @lastOp = 1\n\nwhile @lastOp &gt; 0\nbegin\n    update p\n    set p.tags = isnull(tags + ', ', '' ) + t.[Tagid]\n    from #temp p\n        inner join [TagTable] t\n            on p.[PageId] = t.[PageId]\n    where p.tags not like '%' + t.[Tagid] + '%'\n\n    set  @lastOp == @@rowcount\nend\n\nselect * from #temp\n</code></pre>\n\n<p>Ugly though.</p>\n\n<p>That example's T-SQL, but I think MySql has equivalents to everything used.</p>\n"}, {'answer_id': 37724, 'author': 'Matthew Watson', 'author_id': 3839, 'author_profile': 'https://Stackoverflow.com/users/3839', 'pm_score': 1, 'selected': False, 'text': "<p>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. </p>\n\n<p>If you want a database independent method, you'll have to do it outside of the database.</p>\n\n<p>(untested in all but Oracle)</p>\n\n<p><strong>Oracle</strong></p>\n\n<pre><code>SELECT field1 | ', ' | field2\nFROM table;\n</code></pre>\n\n<p><strong>MS SQL</strong> </p>\n\n<pre><code>SELECT field1 + ', ' + field2\nFROM table;\n</code></pre>\n\n<p><strong>MySQL</strong></p>\n\n<pre><code>SELECT concat(field1,', ',field2)\nFROM table;\n</code></pre>\n\n<p><strong>PostgeSQL</strong></p>\n\n<pre><code>SELECT field1 || ', ' || field2\nFROM table;\n</code></pre>\n"}, {'answer_id': 37761, 'author': 'ConroyP', 'author_id': 2287, 'author_profile': 'https://Stackoverflow.com/users/2287', 'pm_score': 3, 'selected': True, 'text': "<blockquote>\n  <p>Sergio del Amo:</p>\n  \n  <blockquote>\n    <p>However, I am not getting the pages without tags. I guess i need to write my query with left outer joins.</p>\n  </blockquote>\n</blockquote>\n\n<pre><code>SELECT pagetag.id, page.name, group_concat(tag.name)\nFROM\n(\n    page LEFT JOIN pagetag ON page.id = pagetag.pageid\n)\nLEFT JOIN tag ON pagetag.tagid = tag.id\nGROUP BY page.id;\n</code></pre>\n\n<p>Not a very pretty query, but should give you what you want - <code>pagetag.id</code> and <code>group_concat(tag.name)</code> will be <code>null</code> for page 4 in the example you've posted above, but the page shall appear in the results.</p>\n"}, {'answer_id': 37787, 'author': 'Sergio del Amo', 'author_id': 2138, 'author_profile': 'https://Stackoverflow.com/users/2138', 'pm_score': 1, 'selected': False, 'text': '<p>I got a solution playing with joins. The query is: </p>\n\n<pre><code>SELECT\n    page.id AS id,\n    page.name AS name,\n    tagstable.tags AS tags\nFROM page \nLEFT OUTER JOIN \n(\n    SELECT pagetag.pageid, GROUP_CONCAT(distinct tag.name) AS tags\n    FROM tag INNER JOIN pagetag ON tagid = tag.id\n    GROUP BY pagetag.pageid\n)\nAS tagstable ON tagstable.pageid = page.id\nGROUP BY page.id\n</code></pre>\n\n<p>And this will be the output: </p>\n\n<pre><code>id   name    tags\n---------------------------\n1    page 1  tag2,tag3,tag1\n2    page 2  tag1,tag3\n3    page 3  tag4\n4    page 4  NULL\n</code></pre>\n\n<p>Is it possible to boost the query speed writing it another way?</p>\n'}, {'answer_id': 37789, 'author': 'Tom', 'author_id': 3715, 'author_profile': 'https://Stackoverflow.com/users/3715', 'pm_score': 0, 'selected': False, 'text': '<blockquote>\n  <p>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.</p>\n</blockquote>\n\n<p>You can use the <a href="http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce" rel="nofollow noreferrer"><code>COALESCE</code></a> function to remove the Nulls if you need to:</p>\n\n<pre><code>select COALESCE(pagetag.id, \'\') AS id ...\n</code></pre>\n\n<p>It will return the first non-null value from it\'s list of parameters.</p>\n'}]
['https://Stackoverflow.com/questions/37696', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2138/']