0

Please login or register to vote for this query.

(click on this box to dismiss)

http://stackoverflow.com/questions/6896739/sql-find-identical-group

Q&A about the site for people looking to improve their interpersonal communication skills

-- SQL find identical group
-- http://stackoverflow.com/questions/6896739/sql-find-identical-group


declare @a table (id varchar(50), [key] varchar(50), val varchar(50))
insert @a
          select 'bob', 'hair', 'red'
union all select 'bob', 'eyes', 'green'
union all select 'fred', 'hair', 'red'
union all select 'fred', 'eyes', 'green'
union all select 'joe', 'hair', 'green'

declare @b table (id varchar(50), [key] varchar(50), val varchar(50))
insert @b
          select 'bob', 'hair', 'red'
union all select 'bob', 'eyes', 'green'
union all select 'fred', 'hair', 'red'
union all select 'fred', 'eyes', 'green'
union all select 'fred', 'shoe', '42'
union all select 'joe', 'hair', 'red'
union all select 'joe', 'eyes', 'green'
union all select 'greg', 'eyes', 'blue'
union all select 'greg', 'hair', 'brown'

select  IsNull(a.id, b.id)
,       MAX(case when a.id is null or b.id is null then 1 else 0 end) as Differences
from    @a a
full outer join
        @b b
on      a.id = b.id
        and a.[key] = b.[key]
        and a.val = b.val
group by
        IsNull(a.id, b.id)
having  MAX(case when a.id is null or b.id is null then 1 else 0 end) = 0

fork query

created aug 1 11 jon.doe6270