CODEWITHSUNDEEP
phpmysqljoin
Blessan Kurien
Blessan Kurien

Reputation: 1665

Mysql join query with group by

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

Upvotes: 2

Views: 2388

Answers (1)

Ahmed Ginani
Ahmed Ginani

Reputation: 6650

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

Upvotes: 1

Related Questions