We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Outer Join with Count

dpav29
dpav29 asked
on
Medium Priority
399 Views
Last Modified: 2012-05-06
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

Comment
Watch Question

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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

Open in new window

Commented:

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

Author

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.

Author

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

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


Author

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.  

Commented:
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

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


Author

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!
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.