Find Missing Records From Table A For Table B Where They Don't Exist in Table C

Question

I have 3 tables:

Customer (CustomerID)
CustomerEvent (CustomerEventID, CustomerID, EventTypeID)
EventType (EventTypeID)

Some Customer records have some CustomerEvent records with an EventType, some Customer records have no CustomerEvent records.

How do I identify/insert missing CustomerEvent records of each EventType for each Customer record?

My actual issue is a bit more detailed than that, however, this is the piece I'm struggling with.

Can I use one select statement to identify all missing CustomerEvent records? Or would I need to UNION on each EventType record?

score 3 · Accepted Answer · answered Jun 08 '17 at 18:23

3

Using cross join to generate a set of all CustomerId, EventTypeId and filtering out those that exist in CustomerEvent with not exists()

select c.CustomerId, e.EventTypeId
from Customer c
  cross join EventType e
where not exists (
  select 1
  from CustomerEvent ce
  where c.CustomerId = ce.CustomerId
    and e.EventTypeId = ce.EventTypeId
    )

answered Jun 08 '17 at 18:23

SqlZim

37,248
6
41
59

I had not considered a cross join, that solves it. Thank you! – st0rmyc Jun 08 '17 at 18:28

score 0 · Answer 2 · answered Jun 08 '17 at 18:31

0

select * from 

CUSTOMEREVENT CE

left join CUSTOMER C ON C.CustomerID = CE.CustomerID

left join EVENTTYPE ET ON CE.EventTypeID = ET.EventTypeID

where
C.CustomerID IS NULL
OR ET.EventTypeID IS NULL

answered Jun 08 '17 at 18:31

Chains

12,541
8
45
62

Find Missing Records From Table A For Table B Where They Don't Exist in Table C

2 Answers2