Solved

Need help with SQL query syntax

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

CREATE TABLE [dbo].[FCMap_USA](
	[Internal_Id] [varchar](2) NULL,
	[s_name] [varchar](2) NULL,
	[l_name] [varchar](50) NULL,
	[map_swf] [varchar](32) NULL
) ON [PRIMARY]
GO

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 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

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
FROM         FCMap_USA LEFT OUTER JOIN
                      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?
0
Comment
Question by:DanLockwood
1 Comment
 
LVL 14

Accepted Solution

by:
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

0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

776 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