Refer to employees with equal names and different Cpfs

Asked

Viewed 108 times

0

Problem: I need to consult employees who have equal names but with different CPF.

Below is an example of the structure of the table

create table #tmp (nome varchar(255), cpf varchar(255))

insert into #tmp values ('lucas tiago', '35859764847')
insert into #tmp values ('lucas tiago', '12345678980')
insert into #tmp values ('joao da silva', '35859764847')
insert into #tmp values ('joao santos', '12345678981')

nome             cpf
lucas tiago      35859764847
lucas tiago      12345678980
joao da silva    35859764847
joao santos      12345678981

In the example above, the employee Ucas Tiago has 2 registers, however with different Cpf, I want you to bring only these 2 records.

How to consult ?

sql-server
  • 1

    Help yourself: https://answall.com/questions/414077/procura-duplicidade-na-tabela/414082#414082

  • Ué? Wouldn’t it just be a name search? If no CPF is repeated, there will only be a "Fulano Silva" with CPF 123, but if there is another "Fulano Silva" with CPF 456, and you perform the search by the name "Fulano Silva", will return all: Fulano Silva. It’s not that or there’s another issue outside that SELECT .. nome LIKE '%Fulano Silva%'. I was curious and it would be interesting to put in the body of the question the SQL statement that you mounted, as is your SELECT

1 answer

1


In your case, you would have to make a Join with the same table linking the name and using having Count:

SELECT 
    
    #TMP.NOME
    ,#TMP2.CPF
    
FROM 
    #TMP
JOIN
    #TMP AS #TMP2
ON #TMP2.NOME = #TMP.nome

GROUP BY
    #TMP.NOME,
    #TMP2.CPF

HAVING COUNT(#TMP.NOME) > 1```

  • Great, solved my problem. Thank you very much!

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.