GROUP_CONCAT() Example In MySQL With Join

In MySQL, sometimes we need to fetch the particular column value of 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.

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

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

if we want to show the table like this,

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";

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 better understanding, you can visit MySQL | Group_CONCAT() Function GeeksforGeeks

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