Right table join twice into the second table with each internal alias joined to the third table by two aliases
Perhaps I did the wrong thing. Here is what I am trying to do and problems.
I have 3 tables. assets (computer, network device, etc.) ports (port on computer, network device, etc.) port_connections (has a port_id_a and port_id_b field and connects each port and therefore each asset together)
It's really just a way to keep track of vlans and network devices / computers in office buildings.
I am using the newest version of firebird using dialect 3. I assume this is not a firebird issue and a problem with my sql.
I know this should be possible because I can do it with just the correct connections (ports to port_connections) and make other connections in the WHERE clause. The problem with this is that the correct joins are lost when I join the asset table in the ports table.
EDIT: This is the newest query I'm working with because the old ones are useless at the moment. My problem with this newest query is that it appears to be pulling items that are double related to the port_connections table. So I get the proper port_connections entry and then I get a duplicate entry with only one port without port_connection. I need to somehow get rid of this later entry, but still keep the entries for other ports that don't have a port_connection entry.
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
Table: 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 moving the connection_id to 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
source to share
I modified your query to compile and include below. The error you see 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 (ON clause) to determine if the table is left.
Does this return all the rows that you expect, or are you expecting to see some results that aren't?
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 seem to see what's going on here. Since you are connecting to "Connections" in "port a", they return these strings even if the INNER JOIN later (port a for asset a) does not match due to how the RIGHT JOIN behaves. To exclude rows that are not connected I think you just need to change the 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, i.e. where there is no match because nothing is connected.
source to share