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?

Answer 1

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
    )