Right join a table twice to a second table with each alias inner joined to to a third table's two aliases

Question

Maybe I'm going about it the wrong way. Here is what I'm trying to do and my problem.

I have 3 tables. assets (a computer, network device, etc) ports (a port on the computer, network device, etc) port_connections (has a port_id_a and port_id_b field and links each port and therefor each asset together)

It's really just a way to track vlans and network devices/computers in office buildings.

I'm using the newest version of firebird using dialect 3. I'm assuming this isn't a firebird issue and just an problem with my sql.

I know this must be possible because I can do it with just right joins (ports to port_connections) and do the other joins in the WHERE clause. The issue with this is the right joins are lost when I join the assets table to the ports table.

EDIT: this is the newest query I'm working with because the old ones are useless at this point. My issue with this newest query is it seems to be pulling items that are linked though the port_connections table twice. So I will get the proper port_connections record and then I get a duplicate record with just the single port without a port_connection. I need to get rid of this later record somehow but still keep the other port records that don't have a port_connection record.

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

Tables: assets:

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

EDIT: The fix was to move the connection_id into the ports table and this query then does what I wanted.

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

I've modified your query so it compiles, and included it below. The error you're seeing about the unexpected table name is on the INNER JOIN line - you gave the left table name again, and you don't need to, since SQL uses the join criteria (the ON clause) to determine what the left table is.

Does this return all the rows you expect, or are you expecting to see some results that you're not?

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

EDIT: I think I see what's going on here. Because you're RIGHT JOIN-ing from Connections to "port a", it will return those rows even if the INNER JOIN later (port a to asset a) doesn't have a match, because of the way the RIGHT JOIN behaves. To exclude the rows that aren't plugged in, I think you'll just need to modify your WHERE clause:

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

This will filter out rows for which asset_a is NULL, ie where there's no match there because nothing is plugged in.