CODEWITHSUNDEEP
sqlsql-server
user176047
user176047

Reputation: 371

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?

Upvotes: 4

Views: 1363

Answers (3)

Serg
Serg

Reputation: 22811

Using 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

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use a full join to see if the code matches and check if the type is null on either 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 limit the results to codes which 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

Upvotes: 2

shawnt00
shawnt00

Reputation: 17915

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)

Upvotes: 3

Related Questions