CODEWITHSUNDEEP
sql-server
Joseph Thottungal
Joseph Thottungal

Reputation: 1

SQL Query - Inner join matching with multiple columns

I have table which has the following Columns ID, Client ID, Provider ID. I want to pull the records if the same Client ID and Provider ID combination exists more than once. (List both) My result should look like

Column ID  Client ID  Provider ID
   R1         C1         P1
   R2         C1         P1
   R3         C2         P2
   R4         C2         P2

Appreciate your help

Upvotes: 0

Views: 41

Answers (1)

CactusCake
CactusCake

Reputation: 990

This ought to work:

;
WITH X AS(    
  SELECT
  Count(*) over (partition by [Client ID], [Provider ID]) as [Cnt],
  *
  FROM
  YourDatabase..YourTable
  )
SELECT * FROM X WHERE [Cnt] >= 2

Upvotes: 1

Related Questions