Mysql – Combining multiple left join for one single line

indexinnodbjoin;MySQL

We have a query as below.

SELECT tblSData.header,
tblEAlert.eMessage
FROM tblSData
LEFT JOIN tblEAlert ON tblSData.sDataID=tblEAlert.sDataID 
Where tblSData.aID=".$aID." Order By tblSData.dateTimer  Asc

Structure for the tables are as below:

CREATE TABLE IF NOT EXISTS `tblSData` (
  `sDataID` int(11) NOT NULL AUTO_INCREMENT, 
  `header` varchar(255) NOT NULL,   
  `aID` int(5) NOT NULL,
  `gFInID` int(5) NOT NULL,
  `gFOutID` int(5) NOT NULL,
  `gFAInID` int(5) NOT NULL,
  `gFAOutID` int(5) NOT NULL,
  `eAlertID` int(5) NOT NULL,
  `dateTimer` datetime NOT NULL
   PRIMARY KEY (`sDataID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `tblEAlert` (
  `eAlertID` int(11) NOT NULL AUTO_INCREMENT,
  `sDataID` int(5) NOT NULL,
  `eID` int(5) NOT NULL,
  `aID` int(5) NOT NULL,
  `eDateTime` datetime NOT NULL,
  `eInsertDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `eMessage` varchar(255) NOT NULL,
  PRIMARY KEY (`eAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


Sample data for tblSData

1,"A1",1122,100,102,1,2,1,2013-07-13 15:30:19
2,"A3",1122,104,103,3,4,3,2013-07-13 15:45:19
3,"A4",1122,105,108,5,6,7,2013-07-13 15:55:19


Sample data for tblEAlert

1,1,1,1122,2013-07-13 15:30:19,2013-07-13 15:30:19,"Alert 1"
2,1,2,1122,2013-07-13 15:30:19,2013-07-13 15:30:19,"Alert 2"
3,2,2,1122,2013-07-13 15:45:19,2013-07-13 15:45:19,"Alert 3"
4,2,3,1122,2013-07-13 15:45:19,2013-07-13 15:45:19,"Alert 4"
5,2,4,1122,2013-07-13 15:45:19,2013-07-13 15:45:19,"Alert 5"
6,2,5,1122,2013-07-13 15:45:19,2013-07-13 15:45:19,"Alert 6"

Sample output is.

"A1","Alert 1"
"A1","Alert 2"
"A2","Alert 3"
"A2","Alert 4"
"A2","Alert 5"
"A2","Alert 6"

The problem now is how to show say for e.g. A1 all the Alert Message in one line and same goes for A2 etc. Now each alert message is on a different line.

Best Answer

Use the GROUP_CONCAT function

SELECT header,GROUP_CONCAT(eMessage) ErrorMessages FROM
(
    SELECT tblSData.header,IFNULL(tblEAlert.eMessage,'') eMessage
    FROM tblSData
    LEFT JOIN tblEAlert ON tblSData.sDataID=tblEAlert.sDataID 
    Where tblSData.aID=".$aID." Order By tblSData.dateTimer  Asc
) A
GROUP BY header;

or

SELECT tblSData.header,GROUP_CONCAT(IFNULL(tblEAlert.eMessage,'')) ErrorMessages 
FROM tblSData
LEFT JOIN tblEAlert ON tblSData.sDataID=tblEAlert.sDataID 
Where tblSData.aID=".$aID." Order By tblSData.dateTimer  Asc
GROUP BY header;

A good index to create for this would be

ALTER TABLE tblSData ADD INDEX (aID,dateTimer);

What good will this index do ?

  • This will get everything for a specific aID
  • All aID rows are ordered by dateTimer
  • The sDataID column will be available for use from the index rather than the table because the Clustered Index will include the PRIMARY KEY anyway.

You should also create this one

ALTER TABLE tblEAlert ADD INDEX (sDataID);

Give it a Try !!!

Related Question