I am using SQL Server 2005, and have 2 Tables, Tbl_Instances and Tbl_Call_Escalation_Lists:
Tbl_Instances has 2700 entries in it.
Tbl_Call_Escalation_Lists has records that relate to Tbl_Instances.
There may be any number of records in Tbl_Call_Escalation_Lists for each record in Tbl_Instances, BUT, there MUST be at least 1.
Instance_ID in Tbl_Call_Escalation_Lists contains the ID of the related record in Tbl_Instances.
I have a section of code in a long SPROC that is trying to put records in a temporary table with a count of the number of entries in Tbl_Call_Escalation_Lists for each record in Tbl_Instances, and where there are no entries in Tbl_Call_Escalation_Lists, put an entry into the temporary table with a count of 0.
Here is that code:
DECLARE @Tbl2 TABLE(Apps_Cat_ID INT, Instance_ID INT, Resource_Count INT)
DELETE FROM @Tbl2
--Insert all the entries into @Tbl2 where there are Escalation List counts
INSERT INTO @Tbl2
SELECT [Tbl_Call_Escalation_Lists].[Apps_Cat_ID],[Tbl_Call_Escalation_Lists].[Instance_ID],count([Tbl_Call_Escalation_Lists].[ID]) AS Resource_Count
WHERE (Lookup_To_GrgLst_Support_Types_For_Support_Type = 2)
GROUP BY [Tbl_Call_Escalation_Lists].[Apps_Cat_ID], [Tbl_Call_Escalation_Lists].[Instance_ID]
[[[[[The code above works fine.]]]]]
--Now insert zero counts for the remaining apps
INSERT INTO @Tbl2
SELECT [Tbl_Instances].[Apps_Cat_ID], [Tbl_Instances].[ID] AS Instance_ID, 0 AS Resource_Count
WHERE ([ID] NOT IN (SELECT [Instance_ID] AS ID FROM @Tbl2))
[[[[[The code above does NOT work]]]]]
IT seems so simple!! But, why does the second segment of code not work? What do I need to change?
BTW, I could probably make the first segment of code put into @Tbl2 one entry for every record in Tbl_Instances, with 0 as the default for Resource_Count, and then update the counts in @Tbl2 with the counts of whatever I find in Tbl_Call_Escalation_Lists. I will start to explore that, but I still want to know why that code above does not work!