1

i have 3 tables: posts, tags and post_tags

in post_tags i store post tags! and a post can have many tags

Now i want to select all tag names of post 1 while selecting post's own columns.

posts columns are: id, title
tags columns are: id, name
post_tags columns are: id, post_id, tag_id


I have reached up to here, but this returns only one tag name for post, not all tags: (any ideas ?)

select 
     p.*, t.name as tag_name 
from 
     posts p 
     inner join posts_tags pt on p.id = pt.post_id 
     inner join tags t on pt.tag_id = t.id 
where 
     p.id = 1
3

Your answer is correct, and should show as many tags as there are, in different rows. You can check it at Rextester.

If you want all your tags to be converted into a single column (all tags concatened), and have just one row per post, you need to aggregate (GROUP BY) and use then GROUP_CONCAT function.

Your query will look like (I've slightly changed the naming of your columns, for legibility):

-- All tags concat
SELECT
     p.post_id, p.post, group_concat(t.tag ORDER BY t.tag SEPARATOR ', ') AS all_tags
FROM 
     posts p 
     inner join posts_tags pt on p.post_id = pt.post_id 
     inner join tags t on pt.tag_id = t.tag_id 
WHERE 
     p.post_id = 1 
GROUP BY
     p.post_id, p.post ;

Check it here


Addendum

If you want not only tags, but something else, you can aggregate an expressions, and not just a column:

-- All tags, with id concat
SELECT
     p.post_id, p.post, 
     group_concat( concat(t.tag, ' [', t.tag_id, ']') ORDER BY t.tag SEPARATOR ', ') 
        AS all_tags_with_ids
FROM 
     posts p 
     inner join posts_tags pt on p.post_id = pt.post_id 
     inner join tags t on pt.tag_id = t.tag_id 
WHERE 
     p.post_id = 1 
GROUP BY
     p.post_id, p.post ;

Check it here.

  • You don't have arrays in MySQL. But you can group_concat as shown in my answer. – joanolo May 14 '17 at 11:32
  • 1
    You can group_concat an expression. See my extended answer. – joanolo May 14 '17 at 11:40
  • Which version are you using? Rextester uses 5.7.something. And there, it works. – joanolo May 14 '17 at 11:46
  • @MehdiAzmoudeh - "parse error"?? That is not the usual MySQL syntax error message. What language are you going through? Let's see your copy of the statement, plus a full copy of the error provided. – Rick James May 14 '17 at 23:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.