Find groups that are missing values of one column

Hopefully someone can send some light on an issue I’m trying to resolve.

Types

Type 
------
'Type1'
'Type2'
'Type3'

Codes

Code |  ID  | Type
---------------------
ABC  | 1234 | 'Type1'
ABC  | 1234 | 'Type2'
CDE  | 2345 | 'Type1'
CDE  | 2345 | 'Type3'
EFG  | 3456 | 'Type2'

I would like to be able to determine those Code/ID pairs that are missing a particular type. The result of this query should yield.

Code |  ID  | Type
--------------------
ABC  | 1234 | 'Type3'
CDE  | 2345 | 'Type2'
EFG  | 3456 | 'Type1'
EFG  | 3456 | 'Type3'

Answer

You need conditional aggregation for this:

SELECT
    c.Code,
    c.ID,
    t.Type
FROM Types t
CROSS APPLY (
    SELECT *
    FROM Codes c
    CROSS APPLY (VALUES (CASE WHEN c.Type = T.Type THEN 1 END) ) AS v (IsMatch)
    GROUP BY Code, ID
    HAVING COUNT(IsMatch) = 0
) c;

Another option is to cross join the possible IDs with the Types, then EXCEPT to remove the existing ones. Problem is, this means scanning the table twice:

SELECT
    c.Code,
    c.ID,
    t.Type
FROM Types t
CROSS JOIN (
    SELECT DISTINCT Code, ID
    FROM Codes c
) c
EXCEPT
SELECT
    c.Code,
    c.ID,
    c.Type
FROM Codes c;