CODEWITHSUNDEEP
sqlpostgresql
yee379
yee379

Reputation: 6752

SQL join only if there is no match

I have a (postgres) sql table that has the following contents (Hosts):

   ip_address   |  mac_address   | hostname | device | physical_port
----------------+----------------+----------+--------+---------------
111.111.111.111 | aaaa.aaaa.aaaa | hosta    | swh-a  | Gi1/1
111.111.111.112 | bbbb.bbbb.bbbb | hostb    | swh-b  | Gi2/1
111.111.111.113 | cccc.cccc.cccc | hostc    | swh-c  | Gi3/1

I have another table (Peers) that contains point-to-point links between devices in the able table.

 device | physical_port | peer_device | peer_physical_port 
 -------+---------------+-------------+----------------------+
 swh-a  | Gi1/20        | swh-b       | Gi2/1
 swh-b  | Gi2/1         | swh-a       | Gi1/20
 swh-b  | Gi2/1         | swh-c       | Gi3/1
 swh-c  | Gi3/1         | swh-b       | Gi2/1

Basically, I would like the exclude entries from the Hosts table that are contained within the Peers table such that I only get:

   ip_address   |  mac_address   | hostname | device | physical_port
----------------+----------------+----------+--------+---------------
111.111.111.111 | aaaa.aaaa.aaaa | hosta    | swh-a  | Gi1/1

(given that device=swh-b physical_port=Gi2/1 and device=swh-c physical_port=Gi3/1 exist within the Peers table).

Upvotes: 1

Views: 1901

Answers (4)

Shahid Iqbal
Shahid Iqbal

Reputation: 2135

Try this..

SELECT  * 
FROM    Host
WHERE   device NOT IN (SELECT device FROM Peers )
AND     physical_port NOT IN (SELECT physical_port FROM Peers)

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726579

You can use NOT EXISTS for a self-explanatory query that reads almost as if it were in English:

SELECT *
FROM Hosts h
WHERE NOT EXISTS (
    SELECT * FROM Peers p
    WHERE p.peer_device = h.device AND p.peer_physical_port = h.physical_port
)

Upvotes: 4

Roberto
Roberto

Reputation: 2194

Does this work for you?

SELECT * FROM Hosts
WHERE NOT peer_physical_port IN (
    SELECT DISTINCT peer_physical_port FROM Peers
)

You are selecting only the entries that do not appear in the second table.

Upvotes: 1

John
John

Reputation: 1890

You need something like this:

SELECT *
FROM Host h
LEFT JOIN Peers p ON p.device= h.device and p.physical_port = h.physical_port
WHERE p.ID IS NULL

Upvotes: 0

Related Questions