GROUP_CONCAT() example in MySQL with Join


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

iditem_name
1FRUITS
Item table

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

iditem_iditem_subcategory
11Mango
21Apple
31Banana
41Orange
Item Subcategory

if we want to show the table like this,

item_idsubcategory
1Mango, 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 SubcategoryItem name
MangoFRUITS
AppleFRUITS
BananaFRUITS
OrangeFRUITS

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


2 thoughts on “GROUP_CONCAT() example in MySQL with Join

  1. fantastic issues altogether, you simply received a new reader.
    What would you suggest in regards to your submit that
    you simply made a few days in the past? Any certain?

Leave a Reply

Your email address will not be published. Required fields are marked *

Theme of 96 THEME.