Select rows where there is a one-to-one match between two columns in same table SQL

Question

I have a SQL table with two types of ID column. e.g.

ID_1    Name          Date     ID_2
 487     Joe    09/06/2004      332
 731    Mike    06/01/2004      116
 487     Joe    09/06/2004      354
 777    Rich    01/01/2002      455
 745    Mike    06/01/2004      116

Sometimes ID_1 has multiple rows, with different values for ID_2. And vice versa, sometimes ID_2 has multiple rows, with different values for ID_1.

I would like to keep all rows where there is a one-to-one match between ID_1 and ID_2. Ideally, I would also make another table with the remaining rows, so I can easily look at them later. So the above example, only one row (the 4th one) has a one-to-one match between ID_1 and ID_2:

ID_1    Name          Date     ID_2
 777    Rich    01/01/2002      455

All of the other rows have rows where one of the IDs is duplicated. So it is basically removing any rows where either of the ID columns is duplicated at all.

I have tried using DISTINCT, but that keeps one of the duplicate rows, while I want them all removed.

p.s. this is not a question about joining tables - the example is a single table.

Possible duplicate of [LEFT JOIN vs. LEFT OUTER JOIN in SQL Server](https://stackoverflow.com/questions/406294/left-join-vs-left-outer-join-in-sql-server) — PausePause, Jan 23 '19 at 16:45
What does a "one-to-one" match mean? Desired results would help. — Gordon Linoff, Jan 23 '19 at 17:21
Are you looking for rows where the name only appears once? Because I can't see any correlation between id_1 and id_2 in your data. — , Jan 24 '19 at 10:45
I want to keep rows where the IDs only appear once. Removing any rows where either of the ID values are duplicated. — rw2, Jan 24 '19 at 11:01

score 1 · Accepted Answer · answered Jan 24 '19 at 11:59

create table #one_to_one
(id_1 int, name varchar(20), dt date, id_2 int)

insert into #one_to_one values( 487,   'Joe',    '09/06/2004'  ,    332)
insert into #one_to_one values( 731,   'Mike',    '06/01/2004' ,     116)
insert into #one_to_one values(487,   'Joe',    '09/06/2004'  ,    354)
insert into #one_to_one values( 777,    'Rich',    '01/01/2002',      455)
insert into #one_to_one values( 745,    'Mike',    '06/01/2004',      116)


select id_1, name, dt, id_2
from (select *, count(*) over(partition by id_1) as id_1_count,
               count(*) over(partition by id_2) as id_2_count
      from #one_to_one) res
where id_1_count = 1 and id_2_count = 1;

score 0 · Answer 2 · answered Jan 23 '19 at 16:46

You could use windowed COUNT:

CREATE TABLE only_one_to_one
AS
SELECT ID_1, Name, Date, ID_2
FROM (SELECT *,COUNT(*) OVER(PARTITION BY ID_1) AS ID1_cnt,
               COUNT(*) OVER(PARTITION BY ID_2) AS ID2_cnt
      FROM tab) sub
WHERE ID1_cnt = 1 AND ID2_cnt = 1;

db<>fiddle demo

score 0 · Answer 3 · answered Jan 23 '19 at 16:52

0

Only one to one

SELECT *
  FROM Table A
 WHERE (SELECT Count(1)
          FROM Table B
         WHERE A.ID_1 = B.ID_1) = 1
   AND (SELECT Count(1)
          FROM Table B
         WHERE A.ID_2 = B.ID_2) = 1

More than one

SELECT *
  FROM Table A
 WHERE (SELECT Count(1)
          FROM Table B
         WHERE A.ID_1 = B.ID_1) > 1
    OR (SELECT Count(1)
          FROM Table B
         WHERE A.ID_2 = B.ID_2) > 1

answered Jan 23 '19 at 16:52

Mauricio Gerhardt

11
5

Great intuitive answare! What about performance, this can be a prob with large tables – Siyon DP Jan 24 '19 at 12:08
This case can be implemented with query "Exists", I can not remember if a query has better performance. @Siyon.D.P – Mauricio Gerhardt Jan 24 '19 at 15:44

score 0 · Answer 4 · answered Jan 24 '19 at 13:58

this code will help you please

create table #temp (ID_1 int,name varchar(255),[Date] date,ID_2 int)
insert into #temp values (487  ,  'Joe','09/06/2004', 332)
insert into #temp values (731  , 'Mike' ,  '06/01/2004'  ,    116   )
insert into #temp values (487  , ' Joe' ,  '09/06/2004'  ,    354   )
insert into #temp values (777  , 'Rich' ,  '01/01/2002'  ,    455   )
insert into #temp values (745  , 'Mike' ,  '06/01/2004'  ,    116   )


Select * from (
Select ROW_NUMBER() OVER(ORDER BY id_1 DESC)  AS Row#,ID_1,Name,Date,ID_2 
FROM #temp

) as T 

Where Row# = 4
Drop table #temp

Select rows where there is a one-to-one match between two columns in same table SQL

4 Answers4