Solved

Need help with SQL Syntax in a SPROC

Posted on 2007-11-27
3
139 Views
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)

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!
0
Comment
Question by:wsturdev
  • 2
3 Comments
 
LVL 27

Accepted Solution

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

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
 
LVL 1

Author Closing Comment

by:wsturdev
ID: 31411191
The join did it.  Thanks!
0
 
LVL 1

Author Comment

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

The join did it.  Thanks!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
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.​
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

911 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now