CODEWITHSUNDEEP
sqlpostgresql
Mr39
Mr39

Reputation: 61

Find missing records in query

I have the following database table

tbl_setup

id
peer
gw

I have the following records in there

1 | HA | GW1
2 | HA | GW2
3 | HA | GW3
4 | AA | GW1
5 | AB | GW2
6 | AB | GW3
7 | AB | GW4
8 | EE | GW3

Trying to figure out a query to find out which GW are missing data so trying to figure out a query to give me the following results, these would be the GW's missing

HA | GW4
AA | GW2
AA | GW3
AA | GW4
AB | GW1
EE | GW1
EE | GW2
EE | GW4

There should only be 4 GW's GW1 - GW4, will never be more than that

Upvotes: 0

Views: 59

Answers (2)

forpas
forpas

Reputation: 164184

With a cross join of the distinct values of the 2 columns and then not exists:

select *
from (select distinct gw from tablename) g 
cross join (select distinct peer from tablename) p 
where not exists (
  select 1 from tablename
  where gw = g.gw and peer = p.peer
)
order by gw, peer

See the demo.
Or:

select g.gw, p.peer
from (select distinct gw from tablename) g 
cross join (select distinct peer from tablename) p 
left join tablename t
on t.gw = g.gw and t.peer = p.peer
where t.id is null
order by g.gw, p.peer

See the demo.
Results:

| gw  | peer |
| --- | ---- |
| AA  | GW2  |
| AA  | GW3  |
| AA  | GW4  |
| AB  | GW1  |
| EE  | GW1  |
| EE  | GW2  |
| EE  | GW4  |
| HA  | GW4  |

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

The idea is to do a cross join to get all the rows and then filter out the ones that exist. There are several ways to do the filtering. I usually use left join, although not exists,not in, andexcept` are all reasonable alternatives.

If all the peers and gws are in the original table, then you can use that. However, you suggests that there is a separate list, at least for the gw values. You can list those explicitly:

select p.peer, g.gw
from (select distinct peer from t) p cross join
     (values ('GW1'), ('GW2'), ('GW3'), ('GW4')) g(gw) left join
     t
     on t.peer = p.peer and t.gw = g.gw
where t.peer is null;

Upvotes: 0

Related Questions