Mysql join query with group by

Question

I have two tables in my db

posts table (id,title,data) and tags table (id,post_id,tags)

Example data in tags table

Example data in tags table:

id  post_id tag
1   1   PHP
2   1   JS
3   2   C
4   2   C++
5   2   MySql

Posts table

id title date
1  post1 12/05/2015
2  post2  12/05/2016

I want an output like this

Example output:

id  title   tags
1   Post1   PHP, JS
2   Post2   C, C++,MySql

How can i write one single query obtain the result like this

Currently i performs a left join

SELECT * FROM  posts  LEFT JOIN tags ON tags.post_id=posts.id GROUP BY tags.post_id

I know group concat is used to obtain this result but i don't know how to use it

See: Why should I provide an MCVE for what seems to me to be a very simple SQL query? — Strawberry
– Strawberry, Commented Jun 13, 2017 at 8:40
@BlessanKurien I think my answer will help you to join with comma seperated — Ahmed Ginani
– Ahmed Ginani, Commented Jun 13, 2017 at 8:46

Ahmed Ginani · Accepted Answer · 2017-06-13 08:45:46Z

1

use group GROUP_CONCAT to get result as a comma separated value:

SELECT posts.id
     , posts.title
     , GROUP_CONCAT(tags.tag) 
  FROM  posts  
  LEFT 
  JOIN tags 
    ON tags.post_id = posts.id  
 GROUP 
    BY posts.id

edited Jun 13, 2017 at 8:45

answered Jun 13, 2017 at 8:43

Ahmed Ginani

6,6452 gold badges17 silver badges33 bronze badges

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

Collectives™ on Stack Overflow

Mysql join query with group by

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related