query not returning desired results

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)

query not returning desired results

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-08-11 : 13:47:33

Hi,

I have a query that returns results of a few queries turned on to each other as u can see below. The problem is that when I run the queries seperate of each other I get desired results, but they don't JOIN properly or something when run as a whole because I get only about 1% of the desired results.

Please let me know if posting any other data can make things easier.

Is there some obvious mistake here?
Any help or suggestions is much apreciated.

Thanks once again!!
mike123

select u.userID, u.nameOnline, u.lastLoggedIn, u.emailAddress,
isnull(a.IMCount, 0) as IMCount,
isnull(b.MailCount, 0) as MailCount,
isnull(c.commentCount, 0) as CommentCount,
isnull(d.PhotosApprovedCount, 0) as PhotosApprovedCount

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, -3, getdate())
and IM.Date < dateadd(hh, -48, 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, -3, getdate())
and M.Date < dateadd(hh, -48, getdate())
group by UD.userID
having count(*) > 0
) b

on a.userID = b.userID

left join
(
select UD.userID, count(*) as CommentCount
from tblUserDetails UD inner join tblComment C
on C.commentTo = UD.userID
and c.Date > UD.lastLoggedIN
and c.Date > dateadd(dd, -3, getdate())
and c.Date < dateadd(hh, -48, getdate())
group by UD.userID
having count(*) > 0
) c
on a.userID = c.userID

left join
(
select UD.userID, count(*) as PhotosApprovedCount
from tblUserDetails UD inner join tblextraphotos D
on D.userID = UD.userID
and D.photoDate > UD.lastLoggedIN
and d.photoDate > dateadd(dd, -3, getdate())
and d.photoDate < dateadd(hh, -48, getdate())
group by UD.userID
having count(*) > 0
) d
on a.userID = D.userID

where u.emailNotification = 1

--FILTER OUT BOUNCES
AND u.emailaddress NOT IN (SELECT bouncedEmailAddress FROM NDR_EmailMessages WHERE NDR_TypeID = 1)

AND
a.IMCount is not null
or b.MailCount is not null
or c.commentCount is not null
or d.PhotosApprovedCount is not null

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-08-12 : 01:07:59

Ok... for a start, make sure you put brackets around the (a.IMCount is not null or b.MailCount is not null....) section. Or else use "coalecse(a.IMCount, b.MailCount, c.commentCount, d.PhotosApprovedCount) is not null" instead.

But...

Start by removing the tables a, b, c, and d from the query, and make sure that it's working correctly, just filtering on the emailNotification and emailAddress fields. Then introduce table a, and see if that's working ok. Repeat with b, c and d, and see if that helps.

Rob Farley
http://robfarley.blogspot.com

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-08-13 : 20:16:23

got it thanks !!

the problem was in my logic that I kept joining onto a.userID instead of the original table "u.userID" .. overlooked that a few times

much appreciated once again

mike

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-08-16 : 19:53:04

Yes, that wouldn't help. Pleased you've got it sorted. :)

Rob Farley
http://robfarley.blogspot.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 10:02:58

Try to run this query and see if there is any speed improvment.

SELECT		u.UserID,
		u.NameOnline,
		u.LastLoggedIn,
		u.EmailAddress,
		SUM(CASE WHEN im.MessageToID IS NULL THEN 0 ELSE 1 END) IMCount
		SUM(CASE WHEN m.MessageTo IS NULL THEN 0 ELSE 1 END) MailCount
		SUM(CASE WHEN c.MessageTo IS NULL THEN 0 ELSE 1 END) CommentCount
		SUM(CASE WHEN d.UserID IS NULL THEN 0 ELSE 1 END) PhotosApprovedCount
FROM		tblUserDetails u
LEFT JOIN	(
			SELECT	MessageToID,
				Date
			FROM	tblInstantMessage
			WHERE	Checked = 0
				AND Date > DATEADD(day, -3, GETDATE())
				AND Date < DATEADD(hour, -48, GETDATE())
		) im ON im.MessageToID = u.UserID AND im.Date > u.LastLoggedIn
LEFT JOIN	(
			SELECT	MessageTo,
				Date
			FROM	tblMessage
			WHERE	Checked = 0
				AND Date > DATEADD(day, -3, GETDATE())
				AND Date < DATEADD(hour, -48, GETDATE())
		) m ON m.MessageTo = u.UserID AND m.Date > u.LastLoggedIn
LEFT JOIN	(
			SELECT	CommentTo,
				Date
			FROM	tblComment
			WHERE	Date > DATEADD(day, -3, GETDATE())
				AND Date < DATEADD(hour, -48, GETDATE())
		) c ON c.CommentTo = u.UserID AND c.Date > u.LastLoggedIn
LEFT JOIN	(
			SELECT	UserID,
				PhotoDate
			FROM	tblExtraPhotos
			WHERE	PhotoDate > DATEADD(day, -3, GETDATE())
				AND PhotoDate < DATEADD(hour, -48, GETDATE())
		) d ON d.UserID = u.UserID AND d.PhotoDate > u.LastLoggedIn
LEFT JOIN	NDR_EmailMessages n ON n.BouncedEmailAddress = u.EmailAddress
WHERE		u.EmailNotification = 1
		AND n.BouncedEmailAddress IS NULL
GROUP BY	u.UserID,
		u.NameOnline,
		u.LastLoggedIn,
		u.EmailAddress

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources