通过中间 M:N (MySQL) 连接的嵌套查询

我被困在我正在尝试解决的问题上，希望得到一些帮助...

这是数据库的布局：

目标如下：

笔记：

name1 - supplier (e.g., S1, S2, etc.)
name3 - component (e.g., C1, C2, etc.)
name5 - assembly (e.g., A1, A2, etc.)
output - cost for various combinations of supplier-component

我开发的以下（不完整）查询查找关于组件级别 (III) 的最小输出，但它不会执行添加多组件装配级别 (II) 的最小值作为所需查询的中间步骤。

select t5.name5, min(output) as min_output
from t5
inner join t4
    on t5.id5 = t4.id5
inner join t2
    on t4.id3 = t2.id3
group by t5.name5
order by t5.id5
;

结果不正确的输出仅显示以下内容：

name5          min_output
-----|---------------------------
A    | minimum of component A
B    | minimum of component B

期望的结果是 A 和 B 的 III 级组件的 II 级总和（相对于输出最小化），对于每个列出的 I 级/名称 5（即，使用相应的组件/供应商最小化组装成本）。

How can I solve this with the above query, if at all? I feel like I'm missing some nested conditions.

Additionally, how would I determine if any entries from t1 (suppliers) have been unused? Would I use an outer join somehow?

Thanks for your time!

EDIT 2

Per Gordon's suggestion, here is the full data set for reference as code:

CREATE DATABASE IF NOT EXISTS DB;

USE DB;

CREATE TABLE t3 (
    id3 INT AUTO_INCREMENT NOT NULL,
    name3 VARCHAR(255),
    PRIMARY KEY (id3)
);

CREATE TABLE t1 (
    id1 INT AUTO_INCREMENT NOT NULL,
    name1 VARCHAR(255),
    PRIMARY KEY (id1)
);

CREATE TABLE t5 (
    id5 INT AUTO_INCREMENT NOT NULL,
    name5 VARCHAR(255),
    PRIMARY KEY (id5)
);

CREATE TABLE t4 (
    id5 INT,
    id3 INT,
    FOREIGN KEY (id5)
        REFERENCES t5 (id5),
    FOREIGN KEY (id3)
        REFERENCES t3 (id3)
);

CREATE TABLE t2 (
    id2 INT AUTO_INCREMENT NOT NULL,
    id1 INT,
    id3 INT,
    output INT,
    PRIMARY KEY (id2),
    FOREIGN KEY (id1)
        REFERENCES t1 (id1),
    FOREIGN KEY (id3)
        REFERENCES t3 (id3)
);

insert into t3(name3)
    values('C1');
insert into t3(name3)
    values('CS2');
insert into t3(name3)
    values('C3');
insert into t3(name3)
    values('C4');
insert into t3(name3)
    values('C5');
insert into t3(name3)
    values('C6');

insert into t1(name1)
    values('S1');
insert into t1(name1)
    values('S2');
insert into t1(name1)
    values('S3');
insert into t1(name1)
    values('S4');
insert into t1(name1)
    values('S5');

insert into t5(name5)
    values('A1');
insert into t5(name5)
    values('A2'); 
insert into t5(name5)
    values('A3');  
insert into t5(name5)
    values('A4');

insert into t4(id5, id3)
    values(1,1);
insert into t4(id5, id3)
    values(2,2);
insert into t4(id5, id3)
    values(2,3);
insert into t4(id5, id3)
    values(3,4);
insert into t4(id5, id3)
    values(3,5);
insert into t4(id5, id3)
    values(4,6);
insert into t4(id5, id3)
    values(4,3);

insert into t2(id1, id3, output)
    values(1,1,121);
insert into t2(id1, id3, output)
    values(1,2,135);
insert into t2(id1, id3, output)
    values(2,2,94);    
insert into t2(id1, id3, output)
    values(2,3,155);
insert into t2(id1, id3, output)
    values(3,3,178);
insert into t2(id1, id3, output)
    values(3,4,199);
insert into t2(id1, id3, output)
    values(4,4,122);
insert into t2(id1, id3, output)
    values(4,5,155);
insert into t2(id1, id3, output)
    values(5,5,133);
insert into t2(id1, id3, output)
    values(5,6,184);

output from my original query is as follows:

name5          min_output
-----|---------------------------
A1   |       121
A2   |       94
A3   |       122
A4   |       155

expected output is:

name5          min_output
-----|---------------------------
A1   |       121
A2   |       249
A3   |       255
A4   |       339

Additionally, how would I determine if any entries from t1 (suppliers) have been unused? Would I use an outer join with NULL somehow based on the (correct) results table?

expected output:

unused_name1
--------------|
       S3     |

P.S. Good call, thanks Gordon!