help with query ( joins, unions? )

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 2005 Forums

Transact-SQL (2005)

help with query ( joins, unions? )

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-06-14 : 10:55:04

Hi,

Im attempting to write a query that does the following.
I want to create a list that is brought back for each user, that gives them updates on whats happening with their "friends".
Their friends are listed in the table "tblFriends", which I have provided the CREATE statement for above.
(SELECT friendID FROM tblFriends WHERE userID = @userID) returns the list of friends

The results should be brought back in a result set like so

friendID,nameOnline,eventDate,typeID

(nameOnline is JOINED from tblUserDetails) JOIN tblUserDetails UD on tblFriends F = UD.userID = F.friendID

These are the criteria in which I want to bring back a row

When a users friend, adds another friend (as typeID =1)
When a users friend, adds a photo (as typeID =2)
When a users friend, updates their profile (as typeID =3) (SELECT lastLoggedIn FROM tblUserDetails)

For example for userID = 100

The following should be brought back, with an ORDER BY eventDate DESC (sorry my sample data uses all the same dates)

friendID,nameOnline,eventDate,typeID

101,bob,getDate(), 1
105,jack ,getDate(), 1
110,sarah,getDate(), 1
115,mike,getDate(), 1
120,kim,getDate(), 1
101,bob,,getDate(), 2
105,jack,,getDate(), 2
105,jack,getDate(), 2
101,bob,getDate(), 3
105,jack ,getDate(), 3
110,sarah,getDate(), 3
115,mike,getDate(), 3
120,kim,getDate(), 3

I realize this is quite a bit of information, and a little confusing. I've re-read it a couple times and I think this is the best I can figure out how to explain
it all at once. If you have any questions please let me know and I'll be happy to post more info.

Thanks again!
Mike123

Below is the start of the SPROC, passing just the value @userID


CREATE PROCEDURE [dbo].[select_friendUpdates_activity] 
	 (
	 @userID int 
	)
AS SET NOCOUNT ON

SELECT ..?

Here are the CREATE table statements, alogn with values to insert



CREATE TABLE [dbo].[tblFriends](
	[UserID] [int] NOT NULL,
	[FriendID] [int] NOT NULL,
	[dateAdded] [smalldatetime] NULL)

INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (100,101,getDate())
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (100,105,getDate())
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (100,110,getDate())
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (100,115,getDate())
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (100,120,getDate())
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (100,122,getDate())
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (102,100,getDate())

 
CREATE TABLE [dbo].[tblExtraPhotos](
	[counterID] [int] IDENTITY(1,1) NOT NULL,
	[photoID] [tinyint] NOT NULL,
	[userID] [int] NOT NULL,
	[photoDate] [smalldatetime] NOT NULL )


INSERT INTO tblExtraPhotos (photoID,userID,photoDate) VALUES (1,101,getDate())
INSERT INTO tblExtraPhotos (photoID,userID,photoDate) VALUES (1,102,getDate()) 
INSERT INTO tblExtraPhotos (photoID,userID,photoDate) VALUES (1,103,getDate()) 
INSERT INTO tblExtraPhotos (photoID,userID,photoDate) VALUES (1,105,getDate()) 
INSERT INTO tblExtraPhotos (photoID,userID,photoDate) VALUES (2,105,getDate()) 


CREATE TABLE [dbo].[tblUserDetails](
	[UserID] [int] NOT NULL,
	[NameOnline] [varchar](15) NULL,
	[LastLoggedIn] [datetime] NULL,
	[LastUpdated] [smalldatetime] NULL)


INSERT INTO tblUserDetails (userID,nameOnline,lastLoggedIn,lastUpdated) VALUES (100,billy,getDate(),getDate())
INSERT INTO tblUserDetails (userID,nameOnline,lastLoggedIn,lastUpdated) VALUES (101,bob,getDate(),getDate())
INSERT INTO tblUserDetails (userID,nameOnline,lastLoggedIn,lastUpdated) VALUES (105,jack,getDate(),getDate())
INSERT INTO tblUserDetails (userID,nameOnline,lastLoggedIn,lastUpdated) VALUES (110,sarah,getDate(),getDate())
INSERT INTO tblUserDetails (userID,nameOnline,lastLoggedIn,lastUpdated) VALUES (115,mike,getDate(),getDate())
INSERT INTO tblUserDetails (userID,nameOnline,lastLoggedIn,lastUpdated) VALUES (120,kim,getDate(),getDate())
INSERT INTO tblUserDetails (userID,nameOnline,lastLoggedIn,lastUpdated) VALUES (122,rob,getDate(),getDate())
INSERT INTO tblUserDetails (userID,nameOnline,lastLoggedIn,lastUpdated) VALUES (102,tony,getDate(),getDate())
INSERT INTO tblUserDetails (userID,nameOnline,lastLoggedIn,lastUpdated) VALUES (103,rory,getDate(),getDate())

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-14 : 11:40:49

I can provide some direction for you to start with. First, Join tblFriends and tblUserDetails with and hardcode the value 1 in your SELECT to get friends added. Then do another SELECT joining all 3 tables to find user's friend's photos added. UNION ALL these queries to get results from both. Now am not sure how you track the profile updates. There is a lastupdatedDate column but how do you know when was the previous time it was updated? Once you decide that write a SELECT query and add it to above SELECT queries with another UNION ALL.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-06-15 : 09:41:01

Hi Dinakar,

thanks for the direction :) .. the "lastUpdated" column in tbluserdetails is just going to be 1 value, we will not know the previous values, and its ok that they are not going to be returned...

I'm still having some problems writing this query, if anybody is able to help with the syntax , its very much appreciated..

thanks again!
mike123

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-15 : 12:17:07

HEre's what I mean. You might have to tweak the query a little bit.



select Ud.UserID , UD.NameOnline, EventDate = F.dateAdded, TypeId = 1
from tblFriends F
JOIN tblUserDetails UD ON F.FriendID = UD.UserID
Where F.UserID = 100
UNION ALL
select Ud.UserID , UD.NameOnline, EventDate = EP.photoDate, TypeId = 2
from tblFriends F
JOIN tblUserDetails UD ON F.FriendID = UD.UserID
JOIN tblExtraPhotos EP on EP.userID = UD.UserID
Where F.UserID = 100
UNION ALL
select Ud.UserID , UD.NameOnline, EventDate = UD.LastUpdated, TypeId = 3
from tblFriends F
JOIN tblUserDetails UD ON F.FriendID = UD.UserID
Where F.UserID = 100

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-06-15 : 13:16:51

Hi Dinakar,

thanks again for the follow up... I got this working, but I want to run some variations of this query as well .. I'm thinking perhaps this execution plan will not work for my other plans, maybe you can enlighten me :) ..

I want to run it without the "WHERE USERID=100" which ends up giving me 100's of thousands of rows, there for I want to be able to limit the query to just the TOP 100 etc ..

What would be a good efficient way to do this ? Would I have to do a SELECT TOP 100 FROM ( query here... ) ORDER BY date DESC ?

Your thoughts are much appreciated...

Thanks!,
mike123

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-15 : 13:37:29

TOP 100 for each user? or just any TOP 100? If its per user then it gets a little messy with GROUP BY's. If its just any 100 simply put TOP 100 in each of those SELECT queries..


SELECT TOP 100 UD.Userid.....
FROM ..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources