Find rows where one column value matches and the other doesn't

Question

I have two tables A and B

Table a

CODE TYPE
A 1
A 2
A 3
B 1
C 1
C 2

Table B

CODE TYPE
A 1
A 2
A 4
B 2
C 1
C 3

I want to return the rows where CODE is in both tables, but TYPE is not, and also CODE has more than one TYPE in both tables, so my result will be

 CODE TYPE SOURCE
    A 3 Table A
    A 4 Table B
    C 2 Table A
    C 3 Table B

Any help with this?

Answer 1

I think this covers both of your conditions.

select code, coalesce(typeA, typeB) as type, src
from
    (
    select
        coalesce(a.code, b.code) as code,
        a.type as typeA,
        b.type as typeB,
        case when b.type is null then 'A' when a.type is null then 'B' end as src,
        count(a.code) over (partition by coalesce(a.code, b.code)) as countA,
        count(b.code) over (partition by coalesce(a.code, b.code)) as countB
    from
        A a full outer join B b
            on b.code = a.code and b.type = a.type
    ) T
where
        countA >= 2 and countB >= 2
    and (typeA is null or typeB is null)

Answer 2

You can use full jointo find out if the code matches and checks if there is a type nullin any of the tables.

select coalesce(a.code,b.code) code, coalesce(a.type,b.type) type,
case when b.type is null then 'A' when a.type is null then 'B' end src
from a
full join b on a.code = b.code and a.type = b.type
where a.type is null or b.type is null

To restrict results to codes that have more than one type, use

select x.code, coalesce(a.type,b.type) type,
case when b.type is null then 'Table A' when a.type is null then 'Table B' end src
from a
full join b on a.code = b.code and a.type = b.type
join (select a.code from a join b on a.code = b.code 
      group by a.code having count(*) > 1) x on x.code = a.code or x.code = b.code
where a.type is null or b.type is null
order by 1

Answer 3

union

with tu as (
    select CODE, TYPE, src='Table A'
    from TableA
    union all
    select CODE, TYPE, src='Table B'
    from TableB
)
select CODE, TYPE, max(src)
from tu t1
where exists (select 1 from tu t2 where t2.CODE=t1.CODE and t2.src=t1.src and t1.TYPE <> t2.TYPE)
group by CODE, TYPE
having count(*)=1
order by CODE, TYPE