help with select query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help with select query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-07-07 : 13:49:48

Hi,

I have a table called tblMail that holds users internal website mail.

For some reason I have users that aren't checking any of their mail. How can I write a select statement that returns whether a not a specific user has all of their messages in their box unchecked?

Any help appreciated

Thanks again
mike123

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-07 : 14:02:29

The DDL of the table would be helpful

but just shooting from the hip

SELECT UserID FROM tblMail o
WHERE CheckedInd = 'Y'
GROUP BY UserId
HAVING COUNT(*) = (SELECT COUNT(*) FROM tblMail i WHERE i.UserId = o.UserId)

Kinda hard to test with out the actual table

Brett

8-)

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-07-07 : 14:17:08

Hi X002548

Thanks you nailed it perfectly! I'm just wondering this would probably be better brought back as an OUTPUT parameter? Do you agree? If so, do you know how I would do that?

Thanks again,
Mike123

ALTER PROCEDURE dbo.select_IM_Checker
(
@userID int
)
AS SET NOCOUNT ON

SELECT messageToID FROM tblInstantMessage o WHERE checked = '0' and messageToID = @userID GROUP BY messageToID

HAVING COUNT(*) = (SELECT COUNT(*) FROM tblInstantMessage i WHERE i.messageToID = o.messageToID)

GO

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-07 : 14:55:38

Don't you think there could be more than 1. In that case how do you assign many values to 1 output variable?

Brett

8-)

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-07-07 : 15:00:53

Hi Brett,

To make things a little more clear, the reason people would not be receiving any of their mail is because of a popup blocker. This select statement can fairly accurately detect whether they have a popup blocker running.

All I really need is a number of 0 or greater to determine what to do in my other tier. If its 0 or not found, everything is working fine. If not they likely have a popup blocker running. This value could a BOOLEAN value, or it could be a number of total missed messages that I could return to the user. The last option being the best.

Hope that makes it a little clearer.

Thanks again for the help!

mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources