Find missing records in query

Question

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

The data that you are seeing in the first code block is what is in there now, and i just need to be able to run a query to see whats missing — Mr39
– Mr39, Commented Aug 5, 2019 at 16:14

forpas · Accepted Answer · 2019-08-05 16:21:21Z

1

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  |

edited Aug 5, 2019 at 16:21

answered Aug 5, 2019 at 16:17

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Matthias Bö Over a year ago

Arr, you beat me to it ;) Was going to suggest the same with Minus

Mr39 Over a year ago

That was perfect, thank you sooo much, exactly what i needed. Just curious though what exactly does this line "where t.id is null" , i don't it would ever see a null value there since the id's are auto-increment, and they would never be null

forpas Over a year ago

This code joins all the possible combinations of gw and peer to the table. What you want is the non matching pairs because the matching exist in the table. These are the rows with id is null.

forpas Over a year ago

This does not mean that there are nulls in the column id. Just that there is not such an id.

Mr39 Over a year ago

Ohh ok i understand now, thank you very much for the explanation, and i just accepted the answer

Gordon Linoff · Accepted Answer · 2019-08-05 16:20:52Z

0

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;

answered Aug 5, 2019 at 16:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Find missing records in query

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related