Or also a NOT IN (<*fullselect*)
predicate:
Only that my result is ‘Joe’, not ‘Jane’ – and the data constellation would point to that …
WITH
-- your input, sans reserved words
val(id,nam) AS (
SELECT 1,'Peter' FROM sysibm.sysdummy1
UNION ALL SELECT 2,'Jane' FROM sysibm.sysdummy1
UNION ALL SELECT 3,'Joe' FROM sysibm.sysdummy1
)
,
filtr(id,valueid,typ) AS (
SELECT 1,1,'A' FROM sysibm.sysdummy1
UNION ALL SELECT 2,1,'B' FROM sysibm.sysdummy1
UNION ALL SELECT 3,1,'C' FROM sysibm.sysdummy1
UNION ALL SELECT 4,1,'D' FROM sysibm.sysdummy1
UNION ALL SELECT 5,2,'A' FROM sysibm.sysdummy1
UNION ALL SELECT 6,2,'C' FROM sysibm.sysdummy1
UNION ALL SELECT 7,2,'E' FROM sysibm.sysdummy1
UNION ALL SELECT 8,3,'A' FROM sysibm.sysdummy1
UNION ALL SELECT 9,3,'D' FROM sysibm.sysdummy1
)
-- real query starts here
SELECT
*
FROM val
WHERE id NOT IN (
SELECT valueid FROM filtr WHERE typ IN ('B','C')
)
;
-- out id | nam
-- out ----+-------
-- out 3 | Joe
Or also, a failing left join:
SELECT
val.*
FROM val
LEFT JOIN (
SELECT valueid FROM filtr WHERE typ IN ('B','C')
) filtr
ON filtr.valueid = val.id
WHERE valueid IS NULL
CLICK HERE to find out more related problems solutions.