Go Premium for a chance to win a PS4. Enter to Win


Need help with SQL query syntax

Posted on 2011-09-14
Medium Priority
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 2000 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…

971 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