Need help with SQL query syntax

Posted on 2011-09-14
Last Modified: 2012-05-12
I have these two tables:

	[Internal_Id] [varchar](2) NULL,
	[s_name] [varchar](2) NULL,
	[l_name] [varchar](50) NULL,
	[map_swf] [varchar](32) NULL

CREATE TABLE [dbo].[tblEmployee_TerritoryAssignment](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[fkEmployee] [int] NULL,
	[fkFCMap_USA] [varchar](2) NULL,
	[fkFCMap_Detail] [varchar](3) NULL,
 CONSTRAINT [PK_tblEmployee_TerritoryAssignment] PRIMARY KEY CLUSTERED 


Open in new window

What I'm trying to accomplish is to get a list of all rows from FCMap_USA with a COUNT(tblEmployee_TerritoryAssignment.fkFCMap_USA).  Additionally I want to be able to filter by fkEmployee.  If the fkEmployee does not have any rows then I want to return zero.  Right now I have:

SELECT     COUNT(tblEmployee_TerritoryAssignment.fkFCMap_USA) AS myCount, FCMap_USA.map_swf, FCMap_USA.Internal_Id, FCMap_USA.l_name
                      tblEmployee_TerritoryAssignment ON FCMap_USA.Internal_Id = tblEmployee_TerritoryAssignment.fkFCMap_USA
GROUP BY FCMap_USA.map_swf, FCMap_USA.Internal_Id, FCMap_USA.l_name

Open in new window

0	FCMap_Alabama	AL	Alabama
0	FCMap_Alaska	AK	Alaska
0	FCMap_Arizona	AZ	Arizona
0	FCMap_Arkansas	AR	Arkansas
0	FCMap_California	CA	California
0	FCMap_Colorado	CO	Colorado
0	FCMap_Connecticut	CT	Connecticut
0	FCMap_Delaware	DE	Delaware
0	FCMap_DistrictofColumbia	DC	District of Columbia
0	FCMap_Florida	FL	Florida
0	FCMap_Georgia	GA	Georgia
0	FCMap_Hawaii	HI	Hawaii
0	FCMap_Idaho	ID	Idaho
0	FCMap_Illinois	IL	Illinois
0	FCMap_Indiana	IN	Indiana
0	FCMap_Iowa	IA	Iowa
0	FCMap_Kansas	KS	Kansas
0	FCMap_Kentucky	KY	Kentucky
0	FCMap_Louisiana	LA	Louisiana
0	FCMap_Maine	ME	Maine
0	FCMap_Maryland	MD	Maryland
0	FCMap_Massachusetts	MA	Massachusetts
0	FCMap_Michigan	MI	Michigan
0	FCMap_Minnesota	MN	Minnesota
0	FCMap_Mississippi	MS	Mississippi
0	FCMap_Missouri	MO	Missouri
0	FCMap_Montana	MT	Montana
0	FCMap_Nebraska	NE	Nebraska
0	FCMap_Nevada	NV	Nevada
0	FCMap_NewHampshire	NH	New Hampshire
0	FCMap_NewJersey	NJ	New Jersey
0	FCMap_NewMexico	NM	New Mexico
0	FCMap_NewYork	NY	New York
0	FCMap_NorthCarolina	NC	North Carolina
0	FCMap_NorthCarolina	SD	South Dakota
0	FCMap_NorthDakota	ND	North Dakota
0	FCMap_Ohio	OH	Ohio
0	FCMap_Oklahoma	OK	Oklahoma
0	FCMap_Oregon	OR	Oregon
0	FCMap_Pennsylvania	PA	Pennsylvania
0	FCMap_RhodeIsland	RI	Rhode Island
0	FCMap_SouthCarolina	SC	South Carolina
0	FCMap_Tennessee	TN	Tennessee
0	FCMap_Texas	TX	Texas
0	FCMap_Utah	UT	Utah
0	FCMap_Vermont	VT	Vermont
0	FCMap_Virginia	VA	Virginia
0	FCMap_Washington	WA	Washington
0	FCMap_WestVirginia	WV	West Virginia
0	FCMap_Wisconsin	WI	Wisconsin
0	FCMap_Wyoming	WY	Wyoming

Open in new window

This returns zero for the COUNT() for all 50 states.  If I add a WHERE (fkEmployee = 3) then I get no rows returned.  How can I get a count of zero when there are no rows in the joined table but also the COUNT when there are rows?
Question by:DanLockwood
1 Comment
LVL 14

Accepted Solution

Christopher Gordon earned 500 total points
ID: 36539137
Here is my stab at this one.  

I'm getting the total number of records by fkFCMAP_USA in the tblEmployee_TerritoryAssignment and providing an optional filter based on fkEmployee.

I'm then joining the results to this to FCMAP_USA based on fkFCMAP_USA.
declare @EmployeeId int = 0  --change  value to filter

select		map.*
		,	emp_summary.EmployeeCount
from	[dbo].[FCMap_USA] map

--get number of employees per fkFCMAP_USA
left outer join 
	select		fkFCMap_USA
			,	Count(*) as EmployeeCount
	from	[dbo].[tblEmployee_TerritoryAssignment] emp
	--filter on employeeId if one is provided return ALL if 0 is provided
	where	fkEmployee = ISNULL(nullif(@EmployeeId,0), fkEmployee) 
	group by fkFCMap_USA
) emp_summary ON map.Internal_Id = emp_summary.fkFCMap_USA

Open in new window


Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now