Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

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 !!!

share|improve this answer
    
What are the indexes should I use in this case? Which one will be more efficient the first or second ? –  biz14 Sep 3 '13 at 18:42
    
Updated my answer with the needed indexes. As for which query to pick, the second one will have a simpler explain plan in the Query Optimizer. Yet, you should create the index, try both queries, run EXPLAIN on both queries. Then, judge performance from there... –  RolandoMySQLDBA Sep 3 '13 at 18:50
    
I am kind of new to indexing and finding a bit difficult to decide at time what combination index to pick. I know most of the time you look for the where statement. The problem is that at times we have more then 1 where based on a particular field and another where statement picking another field? So in that case we need two separate indexing right? I am not clear here 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.? –  biz14 Sep 4 '13 at 3:40
    
I tried both is taking too long and it only give result with those having the message in tblEAlert which is not suppose to be the case right? –  biz14 Sep 6 '13 at 17:26
    
Oh that's right you did a LEFT JOIN. Let me adjust my answer... –  RolandoMySQLDBA Sep 6 '13 at 17:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.