CODEWITHSUNDEEP
mysqljoin
Ben
Ben

Reputation: 2523

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
;

Upvotes: 1

Views: 58

Answers (1)

Mahesh Madushanka
Mahesh Madushanka

Reputation: 2988

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)

Upvotes: 2

Related Questions