CODEWITHSUNDEEP
sqlsql-server-2005inner-joinself-join
Javi
Javi

Reputation: 197

Self Join bringing too many records

I have this query to express a set of business rules. To get the information I need, I tried joining the table on itself but that brings back many more records than are actually in the table. Below is the query I've tried. What am I doing wrong?

SELECT DISTINCT a.rep_id, a.rep_name, count(*) AS 'Single Practitioner'
FROM [SE_Violation_Detection] a inner join [SE_Violation_Detection] b 
ON a.rep_id = b.rep_id and a.hcp_cid = b.hcp_cid
group by a.rep_id, a.rep_name
having count(*) >= 2

Upvotes: 1

Views: 151

Answers (2)

Javi
Javi

Reputation: 197

I figured out a simpler way to get the information I need for one of the queries. The one above is still wrong.

--Rep violation for different HCP more than 5 times 
select distinct rep_id,rep_name,count(distinct hcp_cid) 
AS 'Multiple Practitioners' 
from dbo.SE_Violation_Detection
group by rep_id,rep_name 
having count(distinct hcp_cid)>4 
order by count(distinct hcp_cid)

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

You can accomplish this with the having clause:

select a, b, count(*) c
from etc
group by a, b
having count(*) >= some number

Upvotes: 4

Related Questions