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.

Leave a Comment

Your email address will not be published.

Scroll to Top