StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POproblems using GROUP_CONCAT in MYSQL
text
Body
copied!<p>i have the following tables:</p> <p><strong>posts</strong>: id, title, date, content</p> <p><strong>tags</strong>: id, name</p> <p><strong>posts_tags</strong>: post_id, tag_id</p> <p>I want to select the title with a list of related tags; the following query works nicely</p> <pre><code>SELECT p.title, GROUP_CONCAT( t.name ORDER BY t.name SEPARATOR ',' ) as tags_list FROM posts AS p JOIN posts_tags AS pt ON pt.post_id = p.id JOIN tags AS t ON pt.tag_id = t.id GROUP BY p.id </code></pre> <p>with this query i get something that looks like this</p> <pre><code>title | tags_list ==================|=============================== Old cinema | film,movies cooking shows | cooking,food,kitchen,television Epic War Films | history,movies,war Art in France | art,france,history </code></pre> <p>when I want to only posts with a certain tag (for example "movies") I tried adding a where clause:</p> <pre><code>SELECT p.title, GROUP_CONCAT( t.name ORDER BY t.name SEPARATOR ',' ) as tags_list FROM posts AS p JOIN posts_tags AS pt ON pt.post_id = p.id JOIN tags AS t ON pt.tag_id = t.id WHERE t.name = 'movies' GROUP BY p.id </code></pre> <p>i end up with this</p> <pre><code>title | tags_list =================|=============================== Old cinema | movies Epic War Films | movies </code></pre> <p>I got all the posts tagged "movies", but the problem is the "tags_list" column only shows "movies" in each row instead of including all the other tags associated with the post.</p> <p>how can I fix this?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload