CODEWITHSUNDEEP
sqldb2
Daniel
Daniel

Reputation: 3

Filtering a table via another table's values

I have 2 tables:

Value

+----+-------+
| id | name  |
+----+-------+
|  1 | Peter |
|  2 | Jane  |
|  3 | Joe   |
+----+-------+

Filter

+----+---------+------+
| id | valueid | type |
+----+---------+------+
|  1 |       1 | A    |
|  2 |       1 | B    |
|  3 |       1 | C    |
|  4 |       1 | D    |
|  5 |       2 | A    |
|  6 |       2 | C    |
|  7 |       2 | E    |
|  8 |       3 | A    |
|  9 |       3 | D    |
+----+---------+------+

I need to retrieve the values from the Value table where the related Filter table does not contain the type 'B' or 'C' So in this quick example this would be only Joe.

Please note this is a DB2 DB and i have limited permissions to run selects only.

Upvotes: 0

Views: 67

Answers (2)

marcothesane
marcothesane

Reputation: 6749

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

Upvotes: 1

The Impaler
The Impaler

Reputation: 48865

You can use EXISTS, as in:

select *
from value v
where not exists (
  select null from filter f
  where f.valueid = v.id and f.type in ('B', 'C')
);

Result:

 ID  NAME 
 --- -----
 3   Joe

See running example at db<>fiddle.

Upvotes: 1

Related Questions