Need help with SQL Syntax in a SPROC

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
FROM [Tbl_Call_Escalation_Lists]
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
FROM [Tbl_Instances]
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!
LVL 1
wsturdevAsked:
Who is Participating?
 
MikeTooleConnect With a Mentor Commented:
Could it be that it's getting the ID's confused in this?
<WHERE ([ID] NOT IN (SELECT [Instance_ID] AS ID FROM @Tbl2))>

You could try:
WHERE ([ID] NOT IN (SELECT [Instance_ID] FROM @Tbl2))

Alternatively, use a join as the source:

INSERT INTO @Tbl2
SELECT [Tbl_Instances].[Apps_Cat_ID], [Tbl_Instances].[ID] AS Instance_ID, 0 AS Resource_Count
FROM [Tbl_Instances]  left join @Tbl2 on [ID]  = Instance_ID
WHERE Instance_ID Is Null
0
 
wsturdevAuthor Commented:
The join did it.  Thanks!
0
 
wsturdevAuthor Commented:
I added a comment in my acceptance, but it does not seem to have posted.

The join did it.  Thanks!
0
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.

All Courses

From novice to tech pro — start learning today.