user176047 user176047 - 3 months ago 8
SQL Question

Find rows where one column value match and other does not

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 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 would 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

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)