CODEWITHSUNDEEP
mysqlsql
shibbir ahmed
shibbir ahmed

Reputation: 1024

How to use GROUP BY Clasuse in Sql query to show unique data with multiple values?

Here is my 2 Mysql table :

logger : 

lg_tbl_id     lg_id           ch_id    lg_name    created
============================================================
29            1583ecb93dd121  8        D4L08841   1480510355
30            1583ecb93dd121  9        D4L08841   1480510355
31            1583eccf794e0e  14       D4L08842   1480510711

channel 

ch_id    ch_name    ch_for        created
============================================
8        CH01       Watter Level  1480247466
9        CH02       Watter Level  1480247474
10       CH31       Watter Level  1480247480
11       CH32       Watter Level  1480247485
12       CHO5       Watter Level  1480506405
13       CHO6       Watter Level  1480506409
14       CHO7       Watter Level  1480506413

Using bellow sql query it's showing following data : 

 $getLogger = mysqli_query($conn, "SELECT logger.lg_name, logger.lg_id, logger.created, channel.ch_name 
 FROM logger 
 LEFT JOIN channel ON channel.ch_id = logger.ch_id 
 ORDER BY logger.lg_tbl_id DESC");

while ( $fetchLogger = mysqli_fetch_array($getLogger) ) {

      $lg_id      = $fetchLogger['lg_id'];
      $lg_name    = htmlspecialchars($fetchLogger['lg_name']);
      $ch_name    = htmlspecialchars($fetchLogger['ch_name']);
      $created    = $fetchLogger['created'];
      $created    = date("Y-m-d h:m:s A", $created);

      // table data showing here....
}

Data Return : 

id              Logger name     Channel Name   created 
1583eccf794e0e  D4L08842        CHO7           2016-11-30 01:11:31 PM
1583ecb93dd121  D4L08841        CH02           2016-11-30 01:11:35 PM
1583ecb93dd121  D4L08841        CH01           2016-11-30 01:11:35 PM

Now you see that id and Logger name column contain duplicate id and Logger name. For e.g 1583ecb93dd121 and D4L08841 showing twice with 2 different Channel Name

I want to show Unique id and Logger name with all Channel Name in one row. Like bellow : 

1583ecb93dd121  D4L08841        CH01, CH02       2016-11-30 01:11:35 PM

Upvotes: 0

Views: 37

Answers (1)

Esty
Esty

Reputation: 1912

You will need GROUP_CONCAT

SELECT logger.lg_name, logger.lg_id, logger.created,
GROUP_CONCAT(channel.ch_name) as ch_name
FROM logger 
LEFT JOIN channel ON channel.ch_id = logger.ch_id 
GROUP BY logger.lg_name, logger.lg_id, logger.created
ORDER BY logger.lg_tbl_id DESC

Upvotes: 1

Related Questions