shibbir ahmed shibbir ahmed - 1 day ago 4
MySQL Question

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

Answer

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
Comments