| Author |
 Topic  |
|
|
helixpoint
Constraint Violating Yak Guru
250 Posts |
 Posted - 11/05/2010 : 15:37:10
|
Not sure if you can figure this out with the info I give you, but I am tring to get the counts. I am trying to do a RIGHT OUTER JOIN
on the Offender_Type_LKP table. I want to bring back all Web_Desc and get a 0 in the count.
SELECT COUNT(*) AS Expr1, dbo.Offender_Type_LKP.Web_Desc, SPGlobalLookups.dbo.County_LKP.County_Name
FROM dbo.Offender_Type_LKP LEFT OUTER JOIN
SPGlobalLookups.dbo.County_LKP INNER JOIN
dbo.OffenderEmployment ON SPGlobalLookups.dbo.County_LKP.County_Code = dbo.OffenderEmployment.County_Code INNER JOIN
dbo.Offender ON dbo.OffenderEmployment.OffenderId = dbo.Offender.OffenderID ON
dbo.Offender_Type_LKP.Offender_Type_LKP_ID = dbo.Offender.Offender_Type_LKP_ID
WHERE (dbo.OffenderEmployment.IsCurrent = 1)
GROUP BY SPGlobalLookups.dbo.County_LKP.County_Name, dbo.Offender_Type_LKP.Web_Desc
ORDER BY SPGlobalLookups.dbo.County_LKP.County_Name, dbo.Offender_Type_LKP.Web_Desc
Dave
Helixpoint Web Development
http://www.helixpoint.com |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/05/2010 : 16:16:13
|
Instead of using "*" in the count, reference an outer column instead.
COUNT(SomeOuterColumnNameHere)
N 56°04'39.26"
E 12°55'05.63" |
 |
|
|
helixpoint
Constraint Violating Yak Guru
250 Posts |
Posted - 11/05/2010 : 16:21:00
|
I tried that Peso. No luck. I am not sure what else to give you
Dave
Helixpoint Web Development
http://www.helixpoint.com |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 11/08/2010 : 04:59:57
|
Change WHERE to AND and see if you get 0 counts
Madhivanan
Failing to plan is Planning to fail |
|
|
|
helixpoint
Constraint Violating Yak Guru
250 Posts |
|
|
TimSman
Posting Yak Master
USA
127 Posts |
Posted - 11/08/2010 : 09:13:23
|
| So, you want everything from Web_Desc that has a count of 0, or you just want to show 0 for the count column? |
|
|
|
helixpoint
Constraint Violating Yak Guru
250 Posts |
Posted - 11/08/2010 : 09:55:35
|
I want to show a 0 if there are no offenders for the county/webtype
Dave
Helixpoint Web Development
http://www.helixpoint.com |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/10/2010 : 03:26:18
|
SELECT COUNT(SPGlobalLookups.dbo.County_LKP.County_Code AS Expr1,
dbo.Offender_Type_LKP.Web_Desc,
SPGlobalLookups.dbo.County_LKP.County_Name
FROM dbo.OffenderEmployment
INNER JOIN dbo.Offender ON dbo.Offender.OffenderID = dbo.OffenderEmployment.OffenderId
INNER JOIN dbo.Offender_Type_LKP ON dbo.Offender_Type_LKP.Offender_Type_LKP_ID = dbo.Offender.Offender_Type_LKP_ID
LEFT JOIN SPGlobalLookups.dbo.County_LKP ON SPGlobalLookups.dbo.County_LKP.County_Code = dbo.OffenderEmployment.County_Code
WHERE dbo.OffenderEmployment.IsCurrent = 1
GROUP BY SPGlobalLookups.dbo.County_LKP.County_Name,
dbo.Offender_Type_LKP.Web_Desc
ORDER BY SPGlobalLookups.dbo.County_LKP.County_Name,
dbo.Offender_Type_LKP.Web_Desc
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 11/10/2010 : 03:34:03
|
quote:
Originally posted by Peso
SELECT COUNT(SPGlobalLookups.dbo.County_LKP.County_Code) AS Expr1,
dbo.Offender_Type_LKP.Web_Desc,
SPGlobalLookups.dbo.County_LKP.County_Name
FROM dbo.OffenderEmployment
INNER JOIN dbo.Offender ON dbo.Offender.OffenderID = dbo.OffenderEmployment.OffenderId
INNER JOIN dbo.Offender_Type_LKP ON dbo.Offender_Type_LKP.Offender_Type_LKP_ID = dbo.Offender.Offender_Type_LKP_ID
LEFT JOIN SPGlobalLookups.dbo.County_LKP ON SPGlobalLookups.dbo.County_LKP.County_Code = dbo.OffenderEmployment.County_Code
WHERE dbo.OffenderEmployment.IsCurrent = 1
GROUP BY SPGlobalLookups.dbo.County_LKP.County_Name,
dbo.Offender_Type_LKP.Web_Desc
ORDER BY SPGlobalLookups.dbo.County_LKP.County_Name,
dbo.Offender_Type_LKP.Web_Desc
N 56°04'39.26"
E 12°55'05.63"
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
helixpoint
Constraint Violating Yak Guru
250 Posts |
Posted - 11/10/2010 : 14:58:03
|
Still not getting :0 Sexual Violent Predator Adams
for Adams
10 Lifetime Offender Adams
16 Out-of-State Offender Adams
11 Ten Year Offender Adams
203 Lifetime Offender Allegheny
86 Out-of-State Offender Allegheny
6 Sexual Violent Predator Allegheny
127 Ten Year Offender Allegheny
Dave
Helixpoint Web Development
http://www.helixpoint.com |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/11/2010 : 00:38:09
|
If someone hasn't told you to post some sample data before, you better do that right now.
I don't think anyone of us like to play guessing games.
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
EugeneLim11
Posting Yak Master
167 Posts |
Posted - 11/11/2010 : 04:05:30
|
| It is best if you could tell us the tables, and post some sample data and what you hope to get. When you make it easy for us to help, we will be more inclined to help :) |
|
|
| |
Topic |
|
trying to get counts
