Select the duplicate rows with specific values

Question

How can I only get the data with the same ID, but not the same Name?

The following is the example to explain my thought. Thanks.

ID     Name     Date          
123    Amy     08/03/2022   
123    Amy     12/03/2022    
456    Billy   08/03/2022    
456    Cat     09/03/2022    
789    Peter   10/03/2022    

Expected Output:

ID     Name     Date
456    Billy   08/03/2022    
456    Cat     09/03/2022 

How I have done.

select ID, Name, count(*)
from table
groupby ID, Name
having count(*) > 1

But the result included the following parts that I do not want it.

ID     Name     Date          
123    Amy     08/03/2022   
123    Amy     12/03/2022 

Answer 1

One approach would be to use a subquery to identify IDs that have multiple names.

SELECT *
FROM YourTable
WHERE ID IN (SELECT ID FROM YourTable GROUP BY ID HAVING COUNT(DISTINCT Name) > 1)