Find the duplicate record from other table

Transact-SQL
1

Hi,
Have two tables csvtable and customer table with below data.

Objective is to list the data in csv table along with a column if its a duplicate record of customer table (ie,firstname,surname and mobile exactly matches)

declare  @csvtable table(id int,firstname varchar(50), surname varchar(50),mobile bigint)
       insert into @csvtable values(100,'Jose','J',1111111111)
       insert into @csvtable values(101,'Francis','F',2222222222)
       insert into @csvtable values(102,'Simon','S',3333333333)
    select * from @csvtable

ID FirstName SurName Mobile
100 Jose J 1111111111
102 Francis F 2222222222
103 Simon S 3333333333

declare  @custtable table(id int,firstname varchar(50), surname varchar(50),mobile bigint)

insert into @custtable values(200,'Jose','J',1111111111)
insert into @custtable values(201,'Thomas','T',4444444444)
insert into @custtable values(202,'Antony','A',5555555555)
select * from @custtable

ID FirstName SurName Mobile
200 Jose J 1111111111
202 Thomas T 4444444444
203 Antony A 5555555555

--Expected Result----

ID FirstName SurName Mobile IsDuplicateCustomer
100 Jose J 1111111111 Y
102 Francis F 2222222222 N
103 Simon S 3333333333 N

2

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-2017

3

hi

i tried to do this

hope it helps
:slight_smile:
:slight_smile:

please give any sort of feedback
i love feedback

drop create data 
DECLARE @csvtable TABLE 
  ( 
     id        INT, 
     firstname VARCHAR(50), 
     surname   VARCHAR(50), 
     mobile    BIGINT 
  ) 

INSERT INTO @csvtable 
VALUES     (100, 
            'Jose', 
            'J', 
            1111111111) 

INSERT INTO @csvtable 
VALUES     (101, 
            'Francis', 
            'F', 
            2222222222) 

INSERT INTO @csvtable 
VALUES     (102, 
            'Simon', 
            'S', 
            3333333333) 

SELECT * 
FROM   @csvtable 

DECLARE @custtable TABLE 
  ( 
     id        INT, 
     firstname VARCHAR(50), 
     surname   VARCHAR(50), 
     mobile    BIGINT 
  ) 

INSERT INTO @custtable 
VALUES     (200, 
            'Jose', 
            'J', 
            1111111111) 

INSERT INTO @custtable 
VALUES     (201, 
            'Thomas', 
            'T', 
            4444444444) 

INSERT INTO @custtable 
VALUES     (202, 
            'Antony', 
            'A', 
            5555555555) 

SELECT * 
FROM   @custtable
SQL ... 
SELECT a.*, 
       CASE 
         WHEN b.id IS NOT NULL THEN 'Y' 
         ELSE 'N' 
       END AS IsDuplicateCustomer 
FROM   @csvtable a 
       LEFT JOIN @custtable b 
              ON a.firstname = b.firstname 
                 AND a.surname = b.surname 
                 AND a.mobile = b.mobile

Result
image

1 Like
4

thanks @harishgg1