CODEWITHSUNDEEP
sqldatabasesqlite
Pylyp
Pylyp

Reputation: 404

SQL query with foreign keys on three tables

I have three tables

Table 1. color_sets_info with set_id (PK), set_name columns

Table 2. colors with set_id (FK), color_id (PK), color_name, color_formula columns

Table 3. mixed_colors with color_id1(FK), color_id2(FK), color_formula columns

foreign key colors.set_id references color_sets_info.set_id

foreign key mixed_colors.color_id1 references colors.color_id

foreign key mixed_colors.color_id2 references colors.color_id

How to get from mixed_colors specific set_name or color_formula and all related columns:

colors.color_name (for mixed_colors.color_id1),

colors.color_name (for mixed_colors.color_id2),

color_sets_info.set_name (for first colors.color_name),

color_sets_info.set_name (for second colors.color_name)

mixed_colors.color_formula?

For example:

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    |
+-----------+-----------+---------------+

I need to get from mixed_colors color_formula and two set_names and two color_names for each mixed color where 1) used only somename1 and somename2 color set 2) with R127G0B0 formula

Upvotes: 2

Views: 98

Answers (1)

Daniel Sparing
Daniel Sparing

Reputation: 2173

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;

Or if you prefer you can first collect colors and colorsets into a 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;

And then query on that:

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;

Upvotes: 2

Related Questions