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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Transaction logs 8 26
Authentication error 1 38
Solution for warm standby SQL server 20 34
SQL Server stored proc 2 10
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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

932 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

16 Experts available now in Live!

Get 1:1 Help Now