help with 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 query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-06-14 : 19:17:27

Hi,

I have 2 queries which are both bringing me back my desired results. I want to combine them to just 1 query, however I am having a tough time doing this.

I need the first query to be added on to the second. Can anyone help me get this going I can't seem to wrap my head around it. The first query just brings back "userID", and "newPhotos". So basically the query after modification should just have 1 extra column "newPhotos"

Thanks very much!

mike123

SELECT pn.userID, COUNT(*) AS newPhotos FROM tblPhoto_Notifications pn
INNER JOIN tblExtraPhotos ep ON pn.friendID = ep.userID
WHERE ep.photoDate >= DATEADD(d, -1, getdate()) GROUP BY pn.userID order by pn.userID

CREATE PROCEDURE dbo.select_Throttled_EmailUpdates_Mail_IM_Comment_Photos
(
@daysAgo int = -3 ,
@hoursBuffer int = -48
)
AS SET NOCOUNT ON

select u.userID, u.nameOnline, u.lastLoggedIn, u.emailAddress,
isnull(a.IMCount, 0) as IMCount,
isnull(b.MailCount, 0) as MailCount
from tblUserDetails u

left join
(
select UD.userID, count(*) as IMCount
from tblUserDetails UD inner join tblInstantMessage IM
on IM.messageToID = UD.userID
and IM.checked = 0
and IM.Date > UD.lastLoggedIN
and IM.Date > dateadd(dd, @daysAgo, getdate())
and IM.Date < dateadd(hh, @hoursBuffer, getdate())
group by UD.userID
having count(*) > 0
) a
on u.userID = a.userID

left join
(
select UD.userID, count(*) as MailCount
from tblUserDetails UD inner join tblMessage M
on M.messageTo = UD.userID
and M.checked = 0
and M.Date > UD.lastLoggedIN
and M.Date > dateadd(dd, @daysAgo, getdate())
and M.Date < dateadd(hh, @hoursBuffer, getdate())
group by UD.userID
having count(*) > 0
) b

on a.userID = b.userID

where u.emailNotification = 1
AND
a.IMCount is not null
or b.MailCount is not null

GO

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-06-15 : 14:26:54

Mike,

???? Not sure what your question is... You said

quote:

I need the first query to be added on to the second. Can anyone help me get this going I can't seem to wrap my head around it. The first query just brings back "userID", and "newPhotos". So basically the query after modification should just have 1 extra column "newPhotos"

Then you show a bunch of TSQL in a stored procedure that doesn't reference anything to do with what I quoted above.

Submit your table structure, some sample data, and the desired output you want, and someone here will be able to help.

Ken

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources