Coalessing with condition in related table

user_613605 0
Viewed 36

I am working in MySQL 5.7.35 and I have the following tables:

create table Table1 (
    Id int not null auto_increment,
    Name varchar(255) not null,
    primary key(Id)
);

create table Table2 (
    Id int not null auto_increment,
    Name varchar(255) not null,
    Table1_Id int not null,
    primary key(Id),
    foreign key(Table1_Id) references Table1(Id)  
);


create table Table3 (
    Id int not null auto_increment,
    Type varchar(255) not null,
    Name varchar(255) not null,
    Result varchar(255) not null,
    Table2_Id int not null,
    primary key(Id),
    foreign key(Table2_Id) references Table2(Id)
);

Inside, I have the following data:

| Id  | Name       |
| --- | ---------- |
| 1   | Computer A |

---

| Id  | Name       | Table1_Id |
| --- | ---------- | --------- |
| 1   | Test Run 1 | 1         |

---

| Id  | Type      | Name      | Result  | Table2_Id |
| --- | --------- | --------- | ------- | --------- |
| 1   | Processor | MMX       | Pass    | 1         |
| 2   | Processor | SSE       | Pass    | 1         |
| 3   | Processor | SSE 2     | Pass    | 1         |
| 4   | Display   | Red       | Pass    | 1         |
| 5   | Display   | Green     | Pass    | 1         |
| 6   | Keyboard  | General   | Pass    | 1         |
| 7   | Keyboard  | Lights    | Skipped | 1         |
| 8   | Network   | Ethernet  | Pass    | 1         |
| 9   | Network   | Wireless  | Skipped | 1         |
| 10  | Network   | Bluetooth | Fail    | 1         |

Desired Query

I would like two columns table1_name and test_result where test_result is a concatenated string with the following logic:

For any given value in Type:

  • If all are passes, then the result is a Pass
  • If any are fails, then the result is a Fail
  • If any are Skipped (poviding the first two points are checked), then the result is Skipped.

So for the current data, the output will be:

| table1_name | test_result                                                      |
| ----------- | ---------------------------------------------------------------- |
| Computer A  | Processor: Pass, Display: Pass, Keyboard: Skipped, Network: Fail |

Current Query

I am struggling to do the coalecing bit when the items I wish to coalesce are in a child table two levels down. My current query is:

select t1.Name as 'table1_name'
-- coalesce to happen here
from Table1 t1
inner join Table2 t2 on t1.Id = t2.Table1_Id
inner join Table3 t3 on t2.Id = t3.Table2_Id;

I have created a db-fiddle to make things easier.

mysql-5.7 group-concat group-by mysql sql
2 Answers

This looks like two levels of aggregation:

select Name, group_concat(name, ': ', result separator ', ')
from (select t1.Name, t3.type,
             (case when min(result) = max(result) then min(result)
                   else 'Skipped'
              end) as result
      from Table1 t1 inner join
           Table2 t2
           on t1.Id = t2.Table1_Id inner join
           Table3 t3
           on t2.Id = t3.Table2_Id
      group by t1.Name, t3.type
     ) nt
group by Name;
user_11440350

Use GROUP_CONCAT() to collect all Results for each Name and Type combination in your preferred order and then in another level of aggregation pick the the first 1:

SELECT table1_name,
       GROUP_CONCAT(Type, ': ', SUBSTRING_INDEX(Results, ',', 1) SEPARATOR ', ') test_result
FROM (
  SELECT t1.Name table1_name, t3.Type,
         GROUP_CONCAT(Result ORDER BY Result = 'Fail' DESC, Result = 'Skipped' DESC) Results
  FROM Table1 t1
  INNER JOIN Table2 t2 on t1.Id = t2.Table1_Id
  INNER JOIN Table3 t3 on t2.Id = t3.Table2_Id
  GROUP BY t1.Name, t3.Type
) t
GROUP BY table1_name;

If you want to preserve the order of Types in the results:

SELECT table1_name,
       GROUP_CONCAT(Type, ': ', SUBSTRING_INDEX(Results, ',', 1) ORDER BY Id SEPARATOR ', ') test_result
FROM (
  SELECT t1.Name table1_name, MIN(t3.Id) Id, t3.Type,
         GROUP_CONCAT(Result ORDER BY Result = 'Fail' DESC, Result = 'Skipped' DESC) Results
  FROM Table1 t1
  INNER JOIN Table2 t2 on t1.Id = t2.Table1_Id
  INNER JOIN Table3 t3 on t2.Id = t3.Table2_Id
  GROUP BY t1.Name, t3.Type
) t
GROUP BY table1_name;

See the demo.

user_104988280
Related