3

Let's say I have two tables that have a one-to-many relationship: People and Attributes. I'm trying to find duplicates based on the first_name, last_name, and all attributes must match exactly.

CREATE TABLE People (Id int, 
                 first_name varchar(100), 
                 last_name varchar(100));

CREATE TABLE Attributes (Id int, 
                     person_id int, 
                     field varchar(100),
                     field_value varchar(100));

INSERT INTO People VALUES (1, 'John', 'Smith');
INSERT INTO People VALUES (2, 'John', 'Smith');
INSERT INTO People VALUES (3, 'John', 'Smith');

INSERT INTO Attributes VALUES (1, 1, 'HairColor', 'Brown');
INSERT INTO Attributes VALUES (2, 1, 'EyeColor', 'Blue');
INSERT INTO Attributes VALUES (3, 2, 'HairColor', 'Brown');
INSERT INTO Attributes VALUES (4, 2, 'EyeColor', 'Blue');
INSERT INTO Attributes VALUES (5, 3, 'HairColor', 'Blonde');

Which gives us:

 id | first_name | last_name
----+------------+-----------
  1 | John       | Smith
  2 | John       | Smith
  3 | John       | Smith

 id | person_id |   field   | field_value
----+-----------+-----------+-------------
  1 |         1 | HairColor | Brown
  2 |         1 | EyeColor  | Blue
  3 |         2 | HairColor | Brown
  4 |         2 | EyeColor  | Blue
  5 |         3 | HairColor | Blonde

I'd like a query that will return IDs 1 and 2 from the People table. I can find duplicates within a single table:

select first_name,last_name,count(*) from People 
    group by first_name,last_name having ( count(*) > 1 );

But I'm having trouble joining the one-to-many table and detecting duplicates across both tables. How can I detect duplicates across tables that have a one-to-many relationship?

4

One way to do this (check the SQLfiddle):

select 
    p1.id as id1, 
    p2.id as id2
from people p1
  join people p2
    on  p1.first_name = p2.first_name
    and p1.last_name = p2.last_name
    and p1.id < p2.id
where not exists
    ( select 1
      from 
      ( select * 
        from attributes a1
        where a1.person_id = p1.id
      union all
        select * 
        from attributes a2
        where a2.person_id = p2.id
      ) g
      group by field, field_value
      having count(*) <> 2
   ) ;

and another:

select 
    p1.id as id1, 
    p2.id as id2
from people p1
  join people p2
    on  p1.first_name = p2.first_name
    and p1.last_name = p2.last_name
    and p1.id < p2.id
where not exists
    ( ( select field, field_value
        from attributes a1
        where a1.person_id = p1.id
      union 
        select field, field_value
        from attributes a2
       where a2.person_id = p2.id
      ) 
    except
      ( select field, field_value
        from attributes a1
        where a1.person_id = p1.id
      intersect
        select field, field_value
        from attributes a2
        where a2.person_id = p2.id
      )
    ) ;

Intersect has higher precedence than except/minus, at least in Postgres and SQL Server. Just to be safe, you can use parentheses, to ensure the precedence.

3

Here's a take using T-SQL syntax, which you could change to use LIMIT 1 rather than TOP 1 if you really need standard syntax. Hope this helps, but it looks like ypercube has you covered either way.

SELECT TOP 1
            MIN(p.Id) AS id1,
            MAX(a.person_id) AS id2
    FROM    dbo.People AS p
            CROSS JOIN dbo.Attributes AS a
    WHERE   p.Id = a.person_id
    GROUP BY p.first_name ,
            p.last_name ,
            a.field ,
            a.field_value
    HAVING  COUNT(*) = ( SELECT COUNT(a2.person_id)
                         FROM   dbo.Attributes AS a2
                         WHERE  a2.field = a.field
                                AND a2.field_value = a.field_value
                       );
0

If your attributes are more-or-less fixed, and you don't mind doing a code release when the attribute list changes, then unrolling them with JOINs and a COUNT would do it, too.

select
    p.Id,
    a1.field_value,
    a2.field_value
from People as p
left outer join Attributes as a1
    on a1.person_id = p.Id
    and a1.field = 'HairColor'
left outer join Attributes as a2
    on a2.person_id = p.Id
    and a2.field = 'EyeColor'
group by
    p.Id,
    a1.field_value,
    a2.field_value
having
    count(*) > 1;

Effective, but not elegant, and potentially slow if you have many people and many attributes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.