SQL query to find False Negatives w.r.t Data Matching or Entity Resolution

Question

Suppose I have two different ID's assigned for same record. For example

RecordID | ID1 | ID2
--------------------
1        | X   | A
2        | X   | B
3        | Y   | C
4        | Y   | C
5        | Y   | D
6        | Z   | E
7        | Z   | E

Now, I want to get the records where ID1 is assigned to the same value where as ID2 is assigned to a different value.

For example, I want to get:

1, X, A
2, X, B

Here ID1 assigned it X where as ID2 assigned it A and B, two different values.

Is it possible to write a query in SQL or SQL server that will return such records?

score 1 · Accepted Answer · edited Feb 06 '13 at 21:40

1

You need to use a subquery where, for each row, you poke through the table and see if any other rows match a certain criteria related to it.

pseudo sql:

select 
      t1.id, 
      t1.variable, 
      t1.value 
   from 
      table t1 
   where 
      exists ( select 1 
                  from t2 
                  where t2.id != t1.id 
                    and t2.variable == t1.variable 
                    and t2.value != t1.value)

edited Feb 06 '13 at 21:40

DRapp

47,638
12
72
142

answered Feb 06 '13 at 21:33

Patashu

21,443
3
45
53

Thank you so much. Your Query worked for me. I truly appreciate it. – Huzaifa Feb 07 '13 at 19:44

score 1 · Answer 2 · answered Feb 06 '13 at 21:45

1

Assuming I'm understanding your requirements, I think all you need is an INNER JOIN:

SELECT DISTINCT T.*
FROM YourTable T
  JOIN YourTable T2 ON T.ID1 = T2.ID1 AND T.ID2 <> T2.ID2

And here is the SQL Fiddle.

Please note, in this example it returns all rows from X and Y. X because of A and B; Y because of C and D. Is this correct?

Good luck.

answered Feb 06 '13 at 21:45

sgeddes

62,311
6
61
83

Yes you are right. I wil test your query tomorrow and tell you if it worked. – Huzaifa Feb 06 '13 at 23:34
Thanks. However, the SQL query is returning all the rows in the table – Huzaifa Feb 07 '13 at 19:36
@Huzaifa -- can you elaborate? Was my SQL Fiddle incorrect for you? Rows with Z aren't returned, where rows with X and Y are because they have different ID2 values associated with them. – sgeddes Feb 07 '13 at 19:38
Its returning all the rows in the database. I have around 4000 records. Its returning me all of them. where exists query worked for me though. – Huzaifa Feb 08 '13 at 20:13

score 1 · Answer 3 · answered Feb 06 '13 at 21:47

1

I think you are looking for this:

SELECT RecordID, ID1, ID2
FROM yourtable
WHERE ID1 IN (SELECT ID1
              FROM yourtable
              GROUP BY ID1
              HAVING COUNT(DISTINCT ID2)>1);

See fiddle here.

answered Feb 06 '13 at 21:47

fthiella

48,073
15
90
106

score 1 · Answer 4 · answered Feb 06 '13 at 22:58

If this is SQL Server 2005+:

WITH minmax AS (
  SELECT
    *,
    minID2 = MIN(ID2) OVER (PARTITION BY ID1),
    maxID2 = MAX(ID2) OVER (PARTITION BY ID1)
  FROM atable
)
SELECT
  RecordID,
  ID1,
  ID2
FROM minmax
WHERE minID2 <> maxID2
;

In the minmax CTE, two more columns are added which hold minimum and maximum ID2 for every group of rows with the same ID1. The main query returns only those rows where the corresponding minimum ID2 doesn't match the maximum ID2.

Its returning me all the rows in the table. – Huzaifa Feb 07 '13 at 19:34 — Huzaifa, Feb 07 '13 at 19:34

SQL query to find False Negatives w.r.t Data Matching or Entity Resolution

4 Answers4