使用链接表中的引用从表中选择(是否在联接中联接?) [英] Select from a table using a reference from a linked table (Join within a Join?)

问题描述

因此，我正在更改数据库中的外键，这在大多数情况下都可以顺利进行.但是，我遇到的一个查询不适用于新的FK参考.

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.

基本上，我要解决的查询是用于引用major表的cable，但现在引用的是sub表(如小提琴中所建).但是，该查询也引用了area表，并且由于cable表不再引用major表，因此我不确定如何保留该引用.

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.

有问题的查询如下:

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
;

但是现在，由于cable表引用了sub表，所以我不知道如何引用major表来选择area?

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

也许是这样吗?

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
;

推荐答案

应该有效

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)

这篇关于使用链接表中的引用从表中选择(是否在联接中联接?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！