group_concat in mysql with join

In this section, We will get comma separated values using inner join of two tables in MySql.

Here first we will take 2 tables and insert data in that table.

First Table:

Create Table-

CREATE TABLE `user_category` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `user_id` bigint(20) NOT NULL,

  `category_name` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`id`)

)

Insert Records-

INSERT INTO `demo`.`user_category`

(`user_id`,`category_name`)

VALUES (1,'Admin');

INSERT INTO `demo`.`user_category`(`user_id`,`category_name`)

VALUES (1,'Super Admin');

INSERT INTO `demo`.`user_category` (`user_id`,`category_name`)

VALUES (1,'User');

INSERT INTO `demo`.`user_category` (`user_id`,`category_name`)

VALUES (2,'Admin');

INSERT INTO `demo`.`user_category` (`user_id`,`category_name`)

VALUES (3,'User');

INSERT INTO `demo`.`user_category` (`user_id`,`category_name`)

VALUES (2,'User');

Second Table:

Create Table-

CREATE TABLE `user_details` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `user_name` bigint(20) NOT NULL,

  `age` bigint(20) DEFAULT NULL,

  PRIMARY KEY (`id`)

)

Insert Records-

INSERT INTO `demo`.`user_details` (`user_name`,`age`)

VALUES ('Santosh',56);

INSERT INTO `demo`.`user_details` (`user_name`,`age`)

VALUES ('Hari',56);

INSERT INTO `demo`.`user_details` (`user_name`,`age`)

VALUES ('Ram',56);

Then that 2 tables look like this:

user_category Table:

id	user_id	category_name
1	1	Admin
2	1	Super Admin
3	1	User
4	2	Admin
5	3	User
6	2	User

user_details Table:

id	user_name	age
1	Santosh	56
2	Hari	56
3	Ram	56

Then for our required result execute below query:

select s.user_name as name, group_concat(distinct t. category_name separator ',') as category from `demo`.user_details as s

inner join `demo`.user_category as t on s.id=t.user_id

Output will be:

name	category
Santosh	Admin,Super Admin, User
Hari	Admin, User
Ram	User