• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

Outer Join with Count

Hi,  I have found many solutions since I started my subscriptions and am very grateful!  I have never asked for help until now. . .can't quite find the right solution!

I'm attempting to get a count by county and state, but I also need to see the services (left table) where the count is zero.  I'm attempting that with a case statement, but no matter what I try I'm only getting results where there is a match on both tables (services and entityservices).

Using SQL Server 2K
Thanks!
select distinct a.state,a.countyname,s.description,s.servicetypename,
case when est.servicetypeid is null then 0 else count (s.servicetypename) End As 'ServiceTypeCount'
 
from servicetypes s -- need all services listed for each county
full outer join entityservicetypes est on est.servicetypeid = s.servicetypeid
 
full outer join projectentities pe on est.projectentityid = pe.projectentityid
left join entityaddresses ea on pe.projectentityid = ea.projectentityid
left join addresses a on ea.addressid = a.addressid
inner join prod_webtrack_dataloading.dbo.xlhealthcounties x on a.countyname = x.county AND a.state = x.state

Open in new window

0
dpav29
Asked:
dpav29
1 Solution
 
Scott PletcherSenior DBACommented:
This join:

inner join prod_webtrack_dataloading.dbo.xlhealthcounties x on a.countyname = x.county AND a.state = x.state

requires a match on both sides for a row to show up.  So, unless the "a." table has a matching row, this INNER join will fail and cause the row to be dropped from the result.

You could try converting this to a LEFT OUTER join.
0
 
Daniel WilsonCommented:
I think it's that last INNER JOIN that's messing you up.  Try a derived table like this:

select distinct a.state,a.countyname,s.description,s.servicetypename,
case when est.servicetypeid is null then 0 else count (s.servicetypename) End As 'ServiceTypeCount'
 
from servicetypes s -- need all services listed for each county
full outer join entityservicetypes est on est.servicetypeid = s.servicetypeid
 
full outer join projectentities pe on est.projectentityid = pe.projectentityid
left join entityaddresses ea on pe.projectentityid = ea.projectentityid
left join (Select a.state,a.countyname from addresses a
inner join prod_webtrack_dataloading.dbo.xlhealthcounties x on a.countyname = x.county AND a.state = x.state) a on ea.addressid = a.addressid

Open in new window

0
 
dqmqCommented:

Not sure exactly what result your looking for, but here's a start:

select a.state,a.countyname,s.description,s.servicetypename,
count (*) As 'ServiceTypeCount'
 
from servicetypes s -- need all services listed for each county
left join entityservicetypes est on est.servicetypeid = s.servicetypeid
 
left join projectentities pe on est.projectentityid = pe.projectentityid
left join entityaddresses ea on pe.projectentityid = ea.projectentityid
left join addresses a on ea.addressid = a.addressid
left join prod_webtrack_dataloading.dbo.xlhealthcounties x on a.countyname = x.county AND a.state = x.state
group by a.state, a.county, s.descripition, s.servicetypename
 

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
dpav29Author Commented:
Thanks for such quick responses!

Daniel. . .I think I get what you're sugguesting, but I don't have an addressid link in the table x.  the only thing in there is state and county. . .can you elaborate?

Scott,  I tried that with no luck. . . and maybe I'm not explaing it right.  If there are 3 services in the services table, I need to see all five once per state/county combination.  I there are no matches for a particular service/county I need to see zero. . .if there are matches, a count.

County1  Service1  0
County1  Service2  3
County1  Service3  5
county2  Service1  6
County2  Service2  0

etc. etc.
0
 
dpav29Author Commented:
Thanks dqmq. . .didn't see your response before replying.  That still doesn't give me any of the zero's.
0
 
dqmqCommented:
I think you are possibly not phrasing your question carefully enough.  You said, there are 3 services in the service table.  Your requirement was to see all services in the result.  Your reply to Scott shows all three;  I expect my SQL does as well.  

Tell us what you REALLY want.
 


0
 
dpav29Author Commented:
OK, sorry if I'm not being clear.. . .in the response above, it was an attempt to show what I want to see:
County1  Service1  0
County1  Service2  3
County1  Service3  5
county2  Service1  6
County2  Service2  0

The results I'm getting though are:

County1  Service2  3
County1  Service3  5
county2  Service1  6

I'm not getting any rows where the count is zero.  Does that make sense?
I've tried the suggestions you all have provided, but with no luck unfortunately.  
0
 
dqmqCommented:
On a hunch, does this give better results?
select s.description, sc.city, sc.county, sc.servicetypecount
from
Servicetypes s cross join
(
select est.servicetypeid, x.city, x.county, count(*) As 'ServiceTypeCount' from entityservicetypes est
inner join projectentities pe on est.projectentityid = pe.projectentityid
inner join entityaddresses ea on pe.projectentityid = ea.projectentityid
inner join addresses a on ea.addressid = a.addressid
inner join prod_webtrack_dataloading.dbo.xlhealthcounties x on a.countyname = x.county AND a.state = x.state
groupby est.servicetypeid, x.city, x.county
)sc
order by s.description, sc.state, sc.county

Open in new window

0
 
dqmqCommented:
Ah ha...

An OUTER join just assures that every Servicetype appears once.  Which it does.   You need a CROSS join to assure that every ServiceType appears for all Counties.  

I can see now that my above suggestion is getting closer, but still not quite right.  Patience please....


0
 
dpav29Author Commented:
I was just writing to say that!  It is very close.

What it is doing now. . .each county and service type is displayed once (Awesome!)
there is a 1 showing up in the count for all rows even if it shoudl be zero, and if there is a value, the serivce always repeats (once for the 1 and once for the true count:
county1  Service1  1
county1  service1  3 (the true count for that service).

Again . . .thanks so much for your effort and hanging in with me!
0
 
dqmqCommented:
Whew...cannot believe this isn't easier, but here's what I have so far.  You're likely to find some syntax errors because I've only deskchecked it, but let's see what happens.
select s.description, all.city, all.county,
isnull(cnt.serviceTypecount,0) ServiceTypeCount
from
ServiceType s inner join
(
Select st.serviceTypeID, sc.city, sc.county
From Servicetype st cross join
(select x.city, x.county from 
entityservicetypes est on st.servicetypeId = est.servicetypeid
inner join projectentities pe on est.projectentityid = pe.projectentityid
inner join entityaddresses ea on pe.projectentityid = ea.projectentityid
inner join addresses a on ea.addressid = a.addressid
inner join prod_webtrack_dataloading.dbo.xlhealthcounties x on a.countyname = x.county AND a.state = x.state
group by x.city, x.county
) sc
) all 
on s.serviceTypeID = all.serviceTypeID
left join 
(
select est.servicetypeId, x.city, x.county, count(*) As 'ServiceTypeCount' from serviceTypes st
inner join entityservicetypes est on st.servicetypeId = est.servicetypeid
inner join projectentities pe on est.projectentityid = pe.projectentityid
inner join entityaddresses ea on pe.projectentityid = ea.projectentityid
inner join addresses a on ea.addressid = a.addressid
inner join prod_webtrack_dataloading.dbo.xlhealthcounties x on a.countyname = x.county AND a.state = x.state
group by est.servicetypeId, x.city, x.county
) cnt 
on cnt.serviceTypeID=all.serviceTypeID and cnt.city=all.city and  cnt.county=all.county
order by s.description, all.state, all.county

Open in new window

0
 
dpav29Author Commented:
Thank you for putting so much thought into this. . .I really appreciate it and also better appreciate the complexity of the problem.  As you suspected, I'm getting some systax errors and I'm working through them now. . . .will get back to you ASAP!
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now