GROUP_CONCAT() example in MySQL with Join

In MySQL, sometimes we need to fetch the particular column value from multiple rows using the same id i.e. the foreign key that belongs to the primary key of the other table. As such, with the help of the GROUP_CONCAT() example in MySQL, we can do this.

This function is used very rare but it is useful while getting multiple values of the same category and incorporate them in a single cell value. There should be two tables in the database to perform this operation.

The first table contains the parent category or the parent id and the second table contains the information related to its parent id.

GROUP_CONCAT() function in MySQL with Join

Suppose we have a table name ‘items’ in our database,

DDL information of the table

CREATE TABLE items (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
item_name varchar(225) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4

id	item_name
1	FRUITS

Suppose, we have another table name ‘item_details’ in our database

DDL information of the table

CREATE TABLE item_details (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
item_id int(10) DEFAULT NULL,
item_subcategory varchar(225) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

id	item_id	item_subcategory
1	1	Mango
2	1	Apple
3	1	Banana
4	1	Orange

if we want to show the table like this,

item_id	subcategory
1	Mango, Apple, Banana, Orange

we will write the GROUP_CONCAT() in mysql query as follows,

"SELECT item_id,GROUP_CONCAT(item_subcategory) AS subcategory FROM item_details GROUP BY item_id";

if we want to show the table like this,

Item Subcategory	Item name
Mango	FRUITS
Apple	FRUITS
Banana	FRUITS
Orange	FRUITS

then we have to write the query using the inner join in MySQL as follows,

"SELECT item_subcategory,item_name FROM item_details itmdetails INNER JOIN items itm ON itmdetails.item_id = itm.id";

To know more about the GROUP_CONCAT function with a better understanding, you can visit MySQL

Conclusion:- I hope this tutorial will help you to understand the GROUP_CONCAT() example in MySql.

Also Read, PHP difference between two dates in years, months and days