三个表上带有外键的 SQL 查询

Question

我有三个表

表 1. color_sets_info 包含 set_id (PK)、set_name 列

表 2. colors with set_id (FK), color_id (PK), color_name , color_formula 列

表 3. mixed_colors 与 color_id1(FK), color_id2(FK), color_formula 列

---

外键 colors.set_id 引用 color_sets_info.set_id

外键 mixed_colors.color_id1 引用 colors.color_id

外键 mixed_colors.color_id2 引用 colors.color_id

---

如何从 mixed_colors 获取特定的 set_name 或 color_formula 和所有相关列:

colors.color_name(对于 mixed_colors.color_id1)，

colors.color_name(对于 mixed_colors.color_id2)，

color_sets_info.set_name(对于第一个 colors.color_name)，

color_sets_info.set_name(第二个 colors.color_name)

mixed_colors.color_formula?

---

例如:

color_sets_info           colors
+--------+-----------+    +--------+----------+------------+---------------+
| set_id | set_name  |    | set_id | color_id | color_name | color_formula |
+--------+-----------+    +--------+----------+------------+---------------+
| 1      | somename1 |    | 1      | 1        | black      | R0G0B0        |
| 2      | somename2 |    | 1      | 2        | yellow     | R255G255B0    |
| 3      | somename3 |    | 2      | 3        | green      | R0G255B255    |
+--------+-----------+    | 3      | 4        | red        | R255G0B0      |
                          +--------+----------+------------+---------------+

mixed_colors
+-----------+-----------+---------------+
| color_id1 | color_id2 | color_formula |
+-----------+-----------+---------------+
| 1         | 4         | R127G0B0      |
| 2         | 3         | R127G255B127  |
| 3         | 1         | R0G127B127    |
+-----------+-----------+---------------+

我需要从 mixed_colors color_formula 和两个 set_names 和每个混合颜色的两个 color_names 得到 1 ) 仅使用 somename1 和 somename2 颜色集 2) 与 R127G0B0 公式

Answer 1

SELECT
    m.color_formula,
    cs1.set_name AS set_name1,
    c1.color_name AS color_name1,
    cs2.set_name AS set_name2,
    c2.color_name AS color_name2
FROM
    mixed_colors m
    JOIN colors c1 ON
        m.color_id1 = c1.color_id
    JOIN color_sets_info cs1 ON
        c1.set_id = cs1.set_id
    JOIN colors c2 ON
        m.color_id2 = c2.color_id
    JOIN color_sets_info cs2 ON
        c2.set_id = cs2.set_id;

或者如果您愿意，您可以先将颜色和颜色集收集到一个 View 中:

CREATE VIEW vw_colors AS
SELECT
    color_id,
    set_id,
    color_name,
    set_name
FROM
    colors c
    JOIN color_sets_info cs ON
        c.set_id = cs.set_id;

然后查询:

SELECT
    m.color_formula,
    v1.set_name AS set_name1,
    v1.color_name AS color_name1,
    v2.set_name AS set_name2,
    v2.color_name AS color_name2
FROM
    mixed_colors m
    JOIN vw_colors v1 ON
        m.color_id1 = v1.color_id
    JOIN vw_colors v2 ON
        m.color_id2 = v2.color_id;