Cognos 11 - filter between query subjects

Question

Given Table A with columns: ColA1, ColA2, ColA3

And a Table B with columns: ColB1

I want to restrict the data that can be returned from Table A based on data in Table B, like:

ColA1 not in ColB1

Ideally, some way incorporate SQL queries in the filter with select statements

Answer 1

What you want is

SELECT a.ColA1
, a.ColA2
, a.ColA3

FROM TableA a
  LEFT OUTER JOIN TableB b on b.ColB1 = a.ColA1

WHERE b.ColB1 IS NULL

So...
Query1 contains ColA1, ColA2, and ColA3 from TableA.
Query2 contains ColB1 from TableB.
Query3

• joins Query1 and Query2 on ColA1 1..1 = 0..1 ColB1
• Data Items: ColA1, ColA2, ColA3
• Filter: ColB1 IS NOT NULL

Answer 2

not exists is probably what you are looking for

Try something like this

select * from TableA as T1  
where not exists                                                  
  (select * from TableB as T2         
    where t1.key1 = t2.key1 and T1.key2 = t2.key2)