将一个表两次右连接到第二个表，每个别名内部连接到第三个表的两个别名

Question

也许我的处理方式是错误的。这是我正在尝试做的事情和我的问题。

我有3张桌子。 资产（计算机、网络设备等） 端口（计算机、网络设备等上的端口） port_connections（具有 port_id_a 和 port_id_b 字段，并将每个端口和每个资产链接在一起）

这实际上只是一种跟踪办公楼中的 VLAN 和网络设备/计算机的方法。

我正在使用最新版本的 firebird 使用方言 3。我假设这不是 firebird 问题，而只是我的 sql 问题。

我知道这一定是可能的，因为我可以通过正确的连接（端口到 port_connections）来做到这一点，并在 WHERE 子句中执行其他连接。问题是当我将资产表连接到端口表时，正确的连接会丢失。

编辑：这是我正在使用的最新查询，因为旧的查询此时毫无用处。我对这个最新查询的问题是，它似乎正在拉动通过 port_connections 表链接的项目两次。因此，我将获得正确的 port_connections 记录，然后获得只有单个端口而没有 port_connection 的重复记录。我需要以某种方式删除这个后来的记录，但仍然保留没有 port_connection 记录的其他端口记录。

SELECT
port_connections.connection_id,

asset_a.name AS asset_a_name,
port_a.port AS port_a_name,
port_a.asset_id as asset_a,

asset_b.name AS asset_b_name,
port_b.port AS port_b_name,
port_b.asset_id as asset_b,

port_connections.description

FROM
port_connections

right JOIN ports AS port_a
ON port_connections.port_id_a = port_a.port_id

right JOIN ports AS port_b
ON port_connections.port_id_b = port_b.port_id

left JOIN assets as asset_a
ON asset_a.asset_id = port_a.asset_id

left JOIN assets as asset_b
ON asset_b.asset_id = port_b.asset_id



WHERE
(port_a.asset_id = 2 OR port_b.asset_id = 2)
ORDER BY port_a_name, port_b_name

表格： 资产：

ASSET_ID
SYS_ID
LOCATION_ID
NAME
DESCRIPTION
"TYPE"
AQUIRED
DISPOSED
MFG_NAME
TAG_NO

port_connections

"CONNECTION_ID"
PORT_ID_A
PORT_ID_B
DESCRIPTION

ports

PORT_ID
ASSET_ID
PORT
TITLE
DESCRIPTION
"TYPE"
SPEED

编辑：修复是将connection_id移动到ports表中，然后这个查询执行我想要的操作。

SELECT
port_connections.connection_id,

asset_a.name AS asset_a_name,
port_a.port AS port_a_name,
port_a.asset_id as asset_a,

asset_b.name AS asset_b_name,
port_b.port AS port_b_name,
port_b.asset_id as asset_b,

port_connections.description

FROM
port_connections



right JOIN ports AS port_b
ON port_connections.connection_id = port_b.connection_id

right JOIN ports AS port_a
ON port_connections.connection_id = port_a.connection_id

left JOIN assets as asset_a
ON asset_a.asset_id = port_a.asset_id

left JOIN assets as asset_b
ON asset_b.asset_id = port_b.asset_id

WHERE

port_a.asset_id = 2
AND
(port_b.asset_id != 2 or port_b.asset_id is null)

ORDER BY port_a_name

Answer 1

我已经修改了您的查询，以便它可以编译，并将其包含在下面。您看到的关于意外表名称的错误位于 INNER JOIN 行 - 您再次给出了左表名称，但您不需要这样做，因为 SQL 使用连接条件（ON 子句）来确定左表名称泰布尔群岛

这是否会返回您期望的所有行，或者您是否期望看到一些您没有看到的结果？

SELECT
port_connections.connection_id,

asset_a.name AS asset_a_name,
port_a.port AS port_a_name,
port_a.asset_id as asset_a,

asset_b.name AS asset_b_name,
port_b.port AS port_b_name,
port_b.asset_id as asset_b,

port_connections.description

FROM
port_connections
RIGHT JOIN ports AS port_a
ON port_connections.port_id_a = port_a.port_id

RIGHT JOIN ports AS port_b
ON port_connections.port_id_b = port_b.port_id

INNER JOIN assets as asset_a
ON asset_a.asset_id = port_a.asset_id

INNER JOIN assets as asset_b
ON asset_b.asset_id = port_b.asset_id

WHERE
(asset_a.asset_id = 2 OR asset_b.asset_id = 2)

ORDER BY port_a_name, port_b_name

编辑：
我想我明白这里发生了什么。因为您是从 Connections 到“端口 a”的 RIGHT JOIN，所以即使稍后的 INNER JOIN（端口 a 到资产 a）不匹配，由于 RIGHT JOIN 的行为方式，它也会返回这些行。要排除未插入的行，我认为您只需要修改 WHERE 子句：

WHERE
(asset_a.asset_id = 2 OR asset_b.asset_id = 2)
  AND asset_a.asset_id IS NOT NULL

这将过滤掉 asset_a 为 NULL 的行，即由于没有插入任何内容而没有匹配的行。