Compare 2 queries from 2 tables

[peewan]

Table A: Query A

S-Code        Epic code
010             B
020             B
030             B
032             B

Table B: Query B
Code        State
010             WY
020             WA
021             WA
022             MN
030             OR
033             OR

I want to compare the value to find the difference value between 2 queries from 2 difference table, to see which code is missing, so I can do an update for documentation later on.

But the join S-CODE TO CODE, wasn't work for me. the join query return all matching code like: 010, 020..only not 021 or 022
Any helps? Thanks a lot!

[CognosPaul]

You need to do what Cognos calls a stitch query.

Full outer join between the tables, with a coalesce or nvl on the key.

Code Select Expand

with querya (scode, epicCode) as  (select scode, epicCode from tablea)
, queryb (scode, state) as (select code, state from tableb)

select coalesce(querya.scode, queryb.scode) as scode
, querya.epiccode
, queryb.state
from querya
full outer join queryb on querya.scode=queryb.scode


[Andrei I]

You could use SQL Minus or Except
https://www.techonthenet.com/sql/minus.php

[peewan]

You need to do what Cognos calls a stitch query.

Full outer join between the tables, with a coalesce or nvl on the key.

Code Select Expand

with querya (scode, epicCode) as  (select scode, epicCode from tablea)
, queryb (scode, state) as (select code, state from tableb)

select coalesce(querya.scode, queryb.scode) as scode
, querya.epiccode
, queryb.state
from querya
full outer join queryb on querya.scode=queryb.scode


Thanks for the response. I don't know how to write/use SQL statement in Cognos :(, i need to learn bout this;  but i took your advise and search about stitch query and full outer join.
Left join: 1:1, 0:n
Right join:0:n, 1:1
Full outer join: 0:n, 0:n

Thank you so much!

[CognosPaul]

You don't need to write any SQL, that was just an example. Use the join item from the insertable objects and set it exactly as you said.

If the data items window make sure to use the coalesce function:

Code Select Expand

coalesce([Query1].[sCode],[Query2].[epicCode])