使用链接表中的引用从表中选择(是否在联接中联接?) [英] 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屋!