Outer Join with Count

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.

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

Select allOpen in new window

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
 

Select allOpen in new window

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.

Thanks dqmq. . .didn't see your response before replying.  That still doesn't give me any of the zero's.

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.
 

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.  

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

Select allOpen in new window

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....

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!

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!