How to display 3 different table data in to single row based on the another column?

Question

I am developing a page using google chart visulization api to display the last 10 runs of test case pass and fail status. I have to fetch from 3 different tables and display the data. Here is the mysql create and insert query. I have attached the output also.

CREATE TABLE `test`.`team` (
  `teamId` INT NOT NULL,
  `teamName` VARCHAR(45) NULL, PRIMARY KEY (`TeamId`));


INSERT INTO test.team values (1,'T1');
INSERT INTO test.team values (2,'T2');
INSERT INTO test.team values (3,'T3');
INSERT INTO test.team values (4,'T4');

CREATE TABLE `test`.`regression` (
  `teamId` INT NOT NULL,
  `rundate` VARCHAR(45) NULL, `runid` VARCHAR(45) NULL,PRIMARY KEY (`runid`));


INSERT INTO test.regression values (1,'2016-06-07','2016-06-07_13:24:07');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-07_18:07:53');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-07_18:16:19');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-07_13:50:59');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-07_14:16:44');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-07_14:38:13');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-07_14:56:46');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-06_13:24:07');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-06_18:07:53');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-06_18:16:19');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-06_13:50:59');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-06_14:16:44');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-06_14:38:13');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-06_14:56:46');

CREATE TABLE `test`.`runs` (
  `runid` VARCHAR(45) NOT NULL,
  `name` VARCHAR(45) NULL, `status` VARCHAR(45) NULL, `elapsed` VARCHAR(45) NULL, PRIMARY KEY (`runid`));


INSERT INTO test.runs values ('2016-06-07_13:24:07','TestCase1','Passed','20');
INSERT INTO test.runs values ('2016-06-07_18:07:53','TestCase1','Passed','20');
INSERT INTO test.runs values ('2016-06-07_18:16:19','TestCase1','Failed','20');
INSERT INTO test.runs values ('2016-06-07_13:50:59','TestCase1','Passed','20');
INSERT INTO test.runs values ('2016-06-07_14:38:13','TestCase1','Failed','20');
INSERT INTO test.runs values ('2016-06-07_14:56:46','TestCase1','Passed','20');
INSERT INTO test.runs values ('2016-06-07_14:16:44','TestCase1','Failed','20');
INSERT INTO test.runs values ('2016-06-06_13:24:07','TestCase2','Failed','20');
INSERT INTO test.runs values ('2016-06-06_18:07:53','TestCase2','Passed','20');
INSERT INTO test.runs values ('2016-06-06_18:16:19','TestCase2','Failed','20');
INSERT INTO test.runs values ('2016-06-06_13:50:59','TestCase2','Passed','20');
INSERT INTO test.runs values ('2016-06-06_14:38:13','TestCase2','Failed','20');
INSERT INTO test.runs values ('2016-06-06_14:56:46','TestCase2','Passed','20');
INSERT INTO test.runs values ('2016-06-06_14:16:44','TestCase2','Passed','20');

Output

T1 TestCase1 2016-06-07 Passed Passed,Failed,Passed,Failed,Passed,Failed
T1 TestCase2 2016-06-07 Failed Passed,Failed,Passed,Failed,Passed,Passed

for displaying 3 table data in one row so you have to execute join query on that. — Kaushal shah, Jun 08 '16 at 05:33

score 0 · Answer 1 · answered Jun 08 '16 at 05:48

0

Use LEFT JOIN in your SELECT query. I can't paste any link but you can search information about LEFT JOIN in Internet

answered Jun 08 '16 at 05:48

Marek Skrzyniarz

56
5

score 0 · Answer 2 · edited May 23 '17 at 12:15

Solution referred from his link Combine Multiple child rows into one row MYSQL

SELECT 
    team.application_name AS 'Project',
    testruns.testcase_name AS 'TestCaseName',
    GROUP_CONCAT(regression.execution_date,
        '-',
        testruns.testcase_status) AS 'Result'
FROM
    testruns,
    regression,
    team
WHERE
    testruns.testrun_id = regression.testrun_information_id
        AND regression.regression_application_id = team.application_id
        AND testruns.testcase_name != ''
GROUP BY testruns.testcase_name

How to display 3 different table data in to single row based on the another column?

2 Answers2