Mysql – two level joins in select query

MySQLPHP

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

Best Answer

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.

Related Question