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 for programmers

-- 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  ids.id
    from    (
            select  distinct id
            from    @a
            union
            select  id
            from    @b
            ) as ids
    left join
            @a a1
    on      a1.id = ids.id
    left join
            @b b1
    on      a1.id = b1.id
            and a1.[key] = b1.[key]
            and a1.val = b1.val
    left join
            @b b2
    on      b2.id = ids.id
    left join
            @a a2
    on      b2.id = a2.id
            and b2.[key] = a2.[key]
            and b2.val = a2.val
    group by
            ids.id
    having  sum(case when b1.id is null or a2.id is null then 1 else 0 end) = 0

fork query

created aug 1 11 jon.doe6270