CODEWITHSUNDEEP
sqlsql-serverdistinct
rw2
rw2

Reputation: 1793

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

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.

Upvotes: 0

Views: 1358

Answers (4)

Mohammad Shehroz
Mohammad Shehroz

Reputation: 236

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

enter image description here

Upvotes: 0

Indra Prakash Tiwari
Indra Prakash Tiwari

Reputation: 1057

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;

Upvotes: 1

Mauricio Gerhardt
Mauricio Gerhardt

Reputation: 11

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

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175766

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

Upvotes: 0

Related Questions