SQL Query - Nodes down

Question

I have the following 3 tables which I would like to run a SQL query to find which nodes have all their interfaces down... which is from the table below is node x2 because (the node is present in the 'outage' table and the count of interfaces is 2 in both the 'outage' and 'interface' table).

node
id name
1  x1
2  x2
3  x3

outage
id nodeid ip       duration
1  1      1.1.1.1  1h
2  2      2.2.2.1  2h
2  2      2.2.2.2  2h
3  3      3.3.3.1  5h

interface
id nodeid ip
1  1      1.1.1.1
1  1      1.1.1.2
1  1      1.1.1.3
2  2      2.2.2.1
2  2      2.2.2.2
3  3      3.3.3.1
3  3      3.3.3.2
3  3      3.3.3.3
3  3      3.3.3.4

I tried to write many sql queries but failed. I have the pesudocode in mind as follows:

if 'the node is present in the outage table' and if 'the count of interfaces in outage table = count of interfaces in interface table then the node deemed to be down. else the node is either up or one or many of its interfaces is down.

any thought or idea on how to approach this issue!

P.S. Using PostgreSQL

Answer 1

The easiest way to do it will be to check if the node doesn't have an interface that is not down.

SELECT *
FROM node AS n
WHERE 
  NOT EXISTS(
    SELECT *
    FROM interface AS i
    LEFT JOIN outage AS o
      ON (i.nodeid,i.ip) = (o.nodeid,o.ip)
    WHERE i.nodeid = n.id AND o.id IS NULL
  )