I am trying to join 3 different tables that holds my test execution results as "PASS", "FAIL" and "SKIP". There are 2 common properties in these 3 tables on the basis of which I need to club my result i.e. "BUILD_NUMBER" and "COMPONENT".

Tried several approach but does not get the desired result.

Best result reached so far.

Sample query:

select test_execution.COMPONENT, test_execution.BUILD_NUMBER, 
count(test_execution.TEST_STATUS) as PASS from (test_execution 
INNER JOIN test_execution_fail ON
test_execution.BUILD_NUMBER = test_execution_fail.BUILD_NUMBER) group by 
COMPONENT,BUILD_NUMBER;

My tables look like below:

CREATE TABLE test_execution_skip (
BUILD_NUMBER int,
TEST_NAME varchar(255),
TEST_CLASS varchar(255),
COMPONENT varchar(255),
TEST_STATUS varchar(255)

);

Other two tables are exactly same with test_execution and test_execution_fail as their names.

test_execution table holds 3 records(all pass values), test_execution_fail table holds 2 records (all fail values) and test_execution_skip table holds 1 record(skip value).

I want to populate data that will show me BUILD_NUMBER, COMPONENT, TOTAL, PASS, FAIL, SKIP as records where TOTAL, PASS, FAIL and SKIP will show the respectives counts.

Any help is appreciated here.

CodePudding user response：

Not sure if this answers your question but you could try something like this

WITH cte AS (
SELECT * FROM test_execution 
union
SELECT * FROM test_execution_fail
UNION
SELECT * FROM test_execution_skip 
)


SELECT t.*,  (SKIP   FAIL   PASS) AS TOTAL  FROM (
select
      COMPONENT,
      BUILD_NUMBER,
      SUM(IF(TEST_STATUS = 'skip', 1, 0 )) as SKIP, 
      SUM(IF(TEST_STATUS = 'fail', 1, 0 )) as FAIL, 
      SUM(IF(TEST_STATUS = 'pass', 1, 0 )) as PASS
      FROM cte
group by COMPONENT,BUILD_NUMBER
)t

db fiddle