Ben Ben - 5 months ago 7
SQL Question

Select from a table using a reference from a linked table (Join within a Join?)

So I'm altering a Foreign Key in my Database, which for the most part is going smoothly. However, there is one query I've come across that won't work with the new FK reference.

I've built a quick Fiddle to show the skeleton of the table structures.

Basically the query I am trying to work out, is the

cable
used to reference the
major
table, but now references the
sub
table instead (as built in the fiddle). However, this query references the
area
table as well, and I'm not sure how to keep that reference now that the
cable
table doesn't reference the
major
table anymore.

The query in question looks like this:

SELECT
C.name,
M.name AS Origin,
M.area AS OriginArea
FROM cable C
INNER JOIN major M ON C.major = M.major_id
WHERE
# Parameters
;


But now, because the
cable
table references the
sub
table I don't know how to reference the
major
table to select the
area
?

Maybe something like this?

SELECT
C.name,
S.name AS Origin,
M.area AS OriginArea
FROM cable C
INNER JOIN sub S ON C.sub = S.sub_id
INNER JOIN major M ON S.major = M.major_id
WHERE
# Parameters
;

Answer

It should work

SELECT   C.name,   S.name AS Origin,   M.area AS OriginArea FROM cable C   INNER JOIN sub S ON C.sub = S.sub_id   INNER JOIN major M ON S.major = M.major_id;
+---------+--------+------------+
| name    | Origin | OriginArea |
+---------+--------+------------+
| cable 1 | sub 1  |          1 |
+---------+--------+------------+
1 row in set (0.07 sec)