Need help with SQL Syntax in a SPROC

Posted on 2007-11-27
Last Modified: 2010-04-21
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)

--Insert all the entries into @Tbl2 where there are Escalation List counts
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
SELECT [Tbl_Instances].[Apps_Cat_ID], [Tbl_Instances].[ID] AS Instance_ID, 0 AS Resource_Count
FROM [Tbl_Instances]

[[[[[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!
Question by:wsturdev
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 27

Accepted Solution

MikeToole earned 500 total points
ID: 20357957
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:

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

Author Closing Comment

ID: 31411191
The join did it.  Thanks!

Author Comment

ID: 20358022
I added a comment in my acceptance, but it does not seem to have posted.

The join did it.  Thanks!

Featured Post

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

627 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