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

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

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

Hi,

I'm having difficulty trying to figure this out.

The code segment below brings back the appropriate information for a user, however I have to modify it. It must bring back the count of photos of the user, however I want it to bring back the count of photos for the users friends, not the user. I have to JOIN it onto "tblFriends.friendID" which is structured as below

tblFriends
userID,friendID

Does this make sense? Any help is much appreciated.. If I can make anything more clear, please let me know :)

thanks very much once again!!

mike123


  left join
       (
           select UD.userID, count(*) as Friends_PhotosApprovedCount
           from   tblUserDetails UD inner join tblextraphotos E
                  on  e.userID    = UD.userID
		  and e.photoDate        > dateadd(dd, -1, getdate())
                 
                  group by UD.userID
           having count(*) > 0
       ) e
	on a.userID = E.userID

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-04 : 20:50:34

maybe


  left join
       (
           select UD.userID, count(*) as Friends_PhotosApprovedCount
           from   tblUserDetails UD 
                      inner join tblFriends F
                              on  UD.userID    = F.userID
                      inner join tblextraphotos E
                              on  E.userID    = F.friendID
		  and E.photoDate        > dateadd(dd, -1, getdate())
                 
                  group by UD.userID
           having count(*) > 0
       ) e
	on a.userID = e.userID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources