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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

607 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