Count in SP different from hand written (same criteria) 1

[dalec]

Strange issue.

I have a sql statement that returns a set of rows for a datagrid (which works correctly).

I have the same criteria to get a count of rows in a stored procedure.

My problem is when I get the count from the stored procedure it always returns the number of records, but, the sql statement returns the correct rows, any ideas why that would be different?

(Thanks in Advance)

SQL Statement:

SELECT Messages_Admin.Subject, Messages_Admin.Message,
       Messages_Admin.CreatedDate, Messages_Admin.CreatedBy, 
Messages_Admin.MsgID 

FROM Messages_Admin LEFT OUTER JOIN Messages_Viewed ON Messages_Admin.MsgID = Messages_Viewed.Msg_ID 

WHERE (Messages_Admin.Org_ID = @Org_ID) AND (Messages_Viewed.Viewed = 0) AND (Messages_Viewed.[User] = @UserName) 
OR (Messages_Admin.Org_ID = @Org_ID) AND (Messages_Viewed.Viewed IS NULL) AND 
(Messages_Viewed.[User] IS NULL) OR 
(Messages_Admin.Org_ID = @Org_ID) AND (Messages_Viewed.[User] <> @UserName)

SQL Stored Procedure:

ALTER PROCEDURE [dbo].[MessageCount]
	-- Add the parameters for the stored procedure here
    @UserName nvarchar, 
	@Org_ID int,
    @Count int output
AS
BEGIN

    -- Insert statements for procedure here
    SET NOCOUNT ON;

SELECT @Count = COUNT(*) 

FROM   Messages_Admin LEFT OUTER JOIN
       Messages_Viewed ON Messages_Admin.MsgID = Messages_Viewed.Msg_ID
WHERE  (Messages_Admin.Org_ID = @Org_ID) AND (Messages_Viewed.Viewed = 0) AND (Messages_Viewed.[User] = @UserName) OR
       (Messages_Admin.Org_ID = @Org_ID) AND (Messages_Viewed.Viewed IS NULL) AND (Messages_Viewed.[User] IS NULL) OR
       (Messages_Admin.Org_ID = @Org_ID) AND (Messages_Viewed.[User] <> @UserName)

END

-FYI, what Im trying to do is display an icon that messages are available for this user, if the user flags that the message has been viewed then I don't want to display the icon that they have messages. The stored procedure gives me the count of messages unread for the user (so I can display the icon or not - doesnt work), and the sql statement is for a datagrid of the messages once they click the icon - (it works)

I've beat myself for hours and don't see why

 

[markros]

You OR and AND conditions are somehow misplaces and therefore I personally can not figure the logic. Can you group them correctly?

PluralSight Learning Library

 

[gmmastros]

try this (in the stored procedure)

SELECT @Count = COUNT(*)

FROM   Messages_Admin 
       LEFT OUTER JOIN Messages_Viewed 
          ON Messages_Admin.MsgID = Messages_Viewed.Msg_ID
WHERE  (Messages_Admin.Org_ID = @Org_ID) 
       AND ((Messages_Viewed.Viewed = 0 AND Messages_Viewed.[User] = @UserName) OR
       (Messages_Viewed.Viewed IS NULL AND Messages_Viewed.[User] IS NULL) OR
       AND (Messages_Viewed.[User] <> @UserName))

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I'm reasonably sure your parenthesis were all mangled up. On top of that, you are (at least partially) causing your left join to act like an inner join. I encourage you to work through the explanation (and example) I give in this thread:

thread183-1504081

It's really important information to know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dalec]

Thank you George, I'll take a look and change this, I notice other issues, that isnt working like I want.

 

[dalec]

That link will help, I'm going to write it from scratch, I think what you posted will be great to base it by.

thank you.

 

[dalec]

Ok, I think I've made myself nutty.

I just can't get the results I want and my mind has officially turned to clay.

What I have is a table that has a simple text field to store messages then I have a second table with user names that have viewed the message. The first table has a msgID and once the user has viewed the message I save to the second table that the message was viewed and record the message id.

What I cant get to work is if user1 views the message and the record is created in table2 I want to know if there are any more outstanding messages for this user.

It seems to work correctly as long as user1 is the only entry in table2 once I add that another user has read the message, things get wacky. Any help would be appreciated, I'm stumpped. (George, I looked at your other post, and it is similar to my problem, but, I'm just not getting the right results).

Really, Thanks in Advance!!!

Dale

 

[gmmastros]

Can you post some sample data and expected results?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dalec]

Table 1
Subject nvarchar(50)
Message nvarchar(Max)
CreatedBy nvarchar(50)
CreatedDate datetime
Msg_ID int (identity)

Table 2
User nvachar(50)
Msg_ID int
Viewed bit
VDate datetime

Table 1 Data
Subject: Hello World
Message: What a great snowy day in Dallas
Createby: Dalec
CreatedDate: 02/11/2010 11:20
Msg_ID: 1

Lets say 2 users have viewed the message, table 2's data would be:

(first record)
user: Mike
Msg_ID: 1
Viewed: 1
Vdate: 02/11/2010 11:30

(second record)
user: Jim
Msg_ID: 1
Viewed: 1
Vdate: 02/11/2010 11:35

When Mike logs in again I want to see if there are any messages that he hasnt read and display a count that he has 0 number of unread messages. Alturnatively if Sue Logs in I want it to give her a count of 1 unread message.

In playing around with this in looking to see how many messages Mike might have it seems to count the other record in the second table and returns 1, I'm stumped to get the right results!

 

[dalec]

PS: there is also a org_ID field in table 1 which is only for segregating organizations but doesnt effect my output I just didnt include it in the example (incase you look at the code in my first question).

 

[dalec]

Been working on this for a couple of days, and I switched things around. Now I have it work (I think), but it's in the reverse, it displays only the viewed messages.

SELECT     Messages_Admin.Subject, Messages_Admin.Message, Messages_Admin.CreatedDate, Messages_Admin.Createdby, Messages_Admin.MsgID, 
                      Messages_Viewed.Msg_ID, Messages_Viewed.[User], Messages_Viewed.Viewed
FROM         Messages_Admin LEFT OUTER JOIN
                      Messages_Viewed ON Messages_Admin.MsgID = Messages_Viewed.Msg_ID
WHERE     (Messages_Admin.Org_ID = @Org_ID) AND (Messages_Viewed.[User] = @UserName) OR
                      (Messages_Admin.Org_ID = @Org_ID) AND (Messages_Viewed.Msg_ID = NULL)

I somehow need to reverse it??

 

[gmmastros]

One of your problems might be this:

[tt][blue]
AND (Messages_Viewed.Msg_ID = NULL)
[/blue][/tt]

When you want to compare data to NULL, you shouldn't use the = comparison operator. Instead, you should use the IS operator.

AND (Messages_Viewed.Msg_ID [!]IS[/!] NULL)

I'm going to take another look at the query and will be making some more suggestions shortly.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

try this:

SELECT Messages_Admin.Subject, 
       Messages_Admin.Message, 
       Messages_Admin.CreatedDate,
       Messages_Admin.Createdby, 
       Messages_Admin.MsgID,
       Messages_Viewed.Msg_ID, 
       Messages_Viewed.[User], 
       Messages_Viewed.Viewed
FROM   Messages_Admin 
       LEFT OUTER JOIN Messages_Viewed 
          ON  Messages_Admin.MsgID = Messages_Viewed.Msg_ID
          And Messages_Admin.Org_ID = @Org_ID
          And Messages_Viewed.[User] = @UserName
WHERE  Messages_Viewed.Msg_ID Is NULL

If this returns the correct output, and you want me to explain it, let me know.

If this does return the correct data, then you should realize that the last 3 columns will ALWAYS return NULL because all of the columns from the messages viewed table will return null. That's the nature of a left join when you filter on the nulls (like we are doing here).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dalec]

That makes sense. Thank you! I was beginning to thank that I was going to have to do a loop thru the data to make it work.

I'm not used to making multiple joins and that was exactly what I needed that you pointed out.

Thank you so much george!!

 

[gmmastros]

The solution was to use multiple joins. That's true. But there was something else.... very subtle going on here. Let me explain.

You have 2 tables in this query. For talking purposes, let's call them Admin and Viewed.

Without any weirdness in the where clause, a LEFT JOIN will return all the rows from the left table and matching rows from the right table. In this query, your "left" table is Admin, and your "right" table is Viewed. You also need to understand that you can get duplicates of the "left" table if there are multiple matches in the right table. Using your sample data, when you execute your query, you would get 2 matches because you were joining on MsgId and there were 2 rows in the viewed table that match. Since there were matches, you wouldn't get a row of NULLs (that you later tried to filter on). This is why your query didn't work.

One important thing to remember about left joins.... IF there are matches, the data will appear in the results. IF there is no match, you will get NULLS.

Let me use your sample data to explain. The code below creates table variables with some sample data. This means you can copy/paste this code in to a query window and run it. I did not include all the columns because they are not important to demonstrate this particular behavior of a left join.

Declare @MessageAdmin Table(Subject nvarchar(50),Msg_ID int)
Declare @MessagesViewed Table([User] nvarchar(50),Msg_ID int)

Insert Into @MessageAdmin Values('Hello World',1)

Insert Into @MessagesViewed Values('Mike',1)
Insert Into @MessagesViewed Values('Jim',1)

Select *
From   @MessageAdmin As Admin
       Left Join @MessagesViewed As Viewed
         On Admin.Msg_ID = Viewed.Msg_ID

When you run the query above, you will get 2 rows because you are joining on MSG_ID and there are 2 matches. Notice that the first 2 columns are duplicates. They are coming from the Admin table. The last 2 columns are coming from the Viewed table, which explains why the user is different.

Now, let's add a filter for NULLS.

Declare @MessageAdmin Table(Subject nvarchar(50),Msg_ID int)
Declare @MessagesViewed Table([User] nvarchar(50),Msg_ID int)

Insert Into @MessageAdmin Values('Hello World',1)

Insert Into @MessagesViewed Values('Mike',1)
Insert Into @MessagesViewed Values('Jim',1)

Select *
From   @MessageAdmin As Admin
       Left Join @MessagesViewed As Viewed
         On Admin.Msg_ID = Viewed.Msg_ID
[!]Where  Viewed.Msg_Id Is NULL[/!]

Notice that this query does not return any data. If we were trying to run this for user Sue, it wouldn't return any rows, and you wouldn't get the results you were looking. Again, this is because you are joining ONLY on Msg_id.

If we add another join condition (without a where clause), we will get this:

Declare @MessageAdmin Table(Subject nvarchar(50),Msg_ID int)
Declare @MessagesViewed Table([User] nvarchar(50),Msg_ID int)

Insert Into @MessageAdmin Values('Hello World',1)

Insert Into @MessagesViewed Values('Mike',1)
Insert Into @MessagesViewed Values('Jim',1)

Select *
From   @MessageAdmin As Admin
       Left Join @MessagesViewed As Viewed
         On Admin.Msg_ID = Viewed.Msg_ID
         And Viewed.[User] = 'Sue'

Now we get the columns from the left table and NULLs in the values from the right table.

No amount of fidiling with a where clause for nulls or anything else would return the data you were looking for because by the time the where clause conditions are applied, there wouldn't be a mismatch and therefore nothing to filter on.

The MOST IMPORTANT thing to take away from this lesson is.... do not put filter conditions in a where clause for a table on the right side of a left join. Instead, put those conditions in the ON clause of the join.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dalec]

Thank you very much George. I didnt know that, and for something that seemed simple to do, it has been quite a
challenge.

-Dale