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 somewhat complicated query

Author  Topic 

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2009-03-10 : 21:32:21
Hi,

I have a simplified query as shown below (the real version is exactly the same concept but 15+ tables, i just trimmed it to 3 so its readable for this posting )

This query is fully working and running great (thx Peso!)

What I want to do is add some functionality to it, and I'm not sure how to integrate this. Basically I have added a query for each user to denote their privacy settings. I have posted the table structure at the bottom of this post.

How it works is this, since this is a new table not every user is going to have a row for their userID, if they don't, it means they don't have any privacy restrictions. They can add privacy restrictions if they like.

If they choose to add privacy restrictions, we will add a row in the table for them. a "1" value denotes the query is allowed, and "0" value denotes it is not allowed. If no row is found, everything is allowed.


So as you can see in the query, we build a list of users to "follow". This is created by combining the "friendslist" and "hotlist".

If a user is in this list, and they opt to not let users "follow" them, this is where we want to impose the restrictions.

Does this make sense? How can I *efficiently* add this functionality to this query?


Any help is greatly appreciated!

Thanks again,
mike123







CREATE PROCEDURE [dbo].[uspGetUserActivityList-test]
(
@userID INT
)
AS

SET NOCOUNT ON

DECLARE @Friends TABLE
(
friendID INT PRIMARY KEY CLUSTERED,
nameOnline VARCHAR(15)
)

INSERT @Friends
(
friendID,
nameOnline
)
SELECT f.friendID,
u.nameOnline
FROM tblFriends AS f
INNER JOIN tblUserDetails AS u ON u.userID = f.friendID
WHERE f.userID = @userID

UNION

SELECT h.hotUserID,
u.nameOnline
FROM tblHotList AS h
INNER JOIN tblUserDetails AS u ON u.userID = h.hotUserID
WHERE h.userID = @userID




--end population of who to join to
SELECT TOP 50 *
FROM (

-- Present the resultset #1 ADDED NEW PHOTOS
SELECT f.nameOnline + 'added a new photo <IMG SRC="http://www.domain.com/pics/' + CAST(CounterID AS varchar(20)) + '_thumb.jpg" border="0" />' AS msg,

ep.MaxDate AS theDate,
1 AS theType,

f.friendID as block_userID,
f.nameOnline as block_nameOnline

FROM @Friends AS f
INNER JOIN (
SELECT userID,counterID,
-- MAX(photoDate) AS maxDate
photoDate as maxDate
FROM tblExtraPhotos WHERE status = 1 AND userID <> @userID

--GROUP BY userID
) AS ep ON ep.userID = f.friendID

UNION ALL

-- Present the resultset #2 ADDED NEW FRIENDS
SELECT f.nameOnline + 'added ' + friend_nameOnline + ' as a new friend ', AS msg

ISNULL(theDate,2001-01-01),
2 AS theType,

f.friendID as block_userID,
f.nameOnline as block_nameOnline


FROM @Friends AS f
INNER JOIN (
SELECT f.userID, ud1.nameOnline as friend_nameOnline,f.friendID,
dateAdded AS theDate
FROM tblFriends f
JOIN tblUserDetails UD1 on UD1.userID = F.friendID

WHERE f.userID <> @userID

) AS u ON u.userID = f.friendID


--merge with above queries
UNION ALL


-- Present the resultset #3a RECEIVED NEW COMMENTS
SELECT f.nameOnline + '</a> received a new comment from ' + c.nameOnline,

theDate,
3 AS theType,

f.friendID as block_userID,
f.nameOnline as block_nameOnline

FROM @Friends AS f
INNER JOIN (
SELECT commentTo,commentFromID,ud1.nameOnline,
com.date AS theDate
FROM tblComment com
JOIN tblUserDetails UD1 on UD1.userID = com.commentFromID

WHERE com.active = 1

) AS c ON c.commentTo = f.friendID




) a


ORDER BY theDate DESC



GO




--CREATE TABLE


CREATE TABLE [dbo].[tblStatusUpdates_PrivacySettings](
[privacyID] [int] IDENTITY(1,1) NOT NULL,
[userID] [int] NULL,
[allow_AddedPhoto] [tinyint] NULL,
[allow_AddedFriend] [tinyint] NULL,
[allow_ReceivedComment] [tinyint] NULL,
[lastUpdated] [datetime] NULL
) ON [PRIMARY]

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2009-03-14 : 20:36:04
Hey Guys,

If anyones able to offer any help on this, very much appreciated!! :)

Thanks again
mike123
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-03-16 : 07:11:03
You can join your derived table a to the new table.

--end population of who to join to
SELECT TOP 50	a.msg,
				a.theDate,
				a.theType,
				a.block_userID,
				a.block_nameOnline,
				[allow_AddedPhoto]=case when [privacyID] is null then 1 else PvtSet.[allow_AddedPhoto] end ,
				[allow_AddedFriend]=case when [privacyID] is null then 1 else PvtSet.[allow_AddedFriend] end,
				[allow_ReceivedComment]=case when [privacyID] is null then 1 else PvtSet.[allow_ReceivedComment] end
FROM	(
				.................
				.................
				.................
		) a
		Left Join [tblStatusUpdates_PrivacySettings] PvtSet on PvtSet.userID=a.block_userID

ORDER BY 
	a.theDate DESC
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-03-16 : 07:12:22
Just check for the join condition between derived table 'a' and your new table. I wasn't sure what the condition is from your first post.
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2009-03-16 : 16:04:18
Hi Sakets,

The complication is that its a JOIN on the user they are followings privacy permissions, not their own settings. Make sense?


Thanks again,
Mike123
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-03-16 : 16:46:45
ISNULL(theDate, '2001-01-01'),

Otherwise you will get day 1999 since January 1st 1900, which is about the year 1905-1906.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2009-03-16 : 18:44:00
Hey Peso,

I will add that suggestion of yours, altho I am not sure we will have any nulls on these columms.


Still pretty confused on how to advance this query. I don't think Sakets suggestion is what I am looking for because the settings we are querying are not for users to limit the results of their own queries, but rather they are privacy restrictions from other users.

For example, if user "A" has 100 friends, and receives status updates for those 100 friends, then when we run the query, we are only going to pull activity updates that the friends have allowed to be published. For example, user "B" who is a friend of user "A", may choose to opt to not allow users to know when they ("user B") have added new friends. In this case, when user "A" runs the query, all situations where user "B" has added a friend, will not be visible to user "A".

Is this a bit more clear perhaps ?? Any suggestion very helpful!


Thanks again,
Mike123
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-03-16 : 18:46:59
Do you have any sample data for us to work with?
Please post them as suggested before.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2009-03-16 : 19:02:05
Will dig some up .. thx! :)
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2009-03-17 : 21:33:57
Hi Peso,

Here is some sample data, with all the CREATE statements for all relevant tables. I have filled all the tables with pretty generic data. I created pretty much the same data for each user so its easy to see when data is not returned.

Data will not be returned according to the privacy settings on the last table displayed. ([tblStatusUpdates_PrivacySettings])

Note that this is not users choosing what they which to receive, but rather other users choosing what updates of theirs are allowed to be followed.


Hope this clears things up a little bit.


Many thanks once again!
mike123







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




INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (1,2,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (1,3,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (1,4,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (1,5,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (1,6,getDate())

INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (2,1,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (2,3,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (2,4,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (2,5,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (2,6,getDate())

INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (3,1,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (3,2,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (3,4,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (3,5,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (3,6,getDate())

INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (4,1,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (4,2,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (4,3,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (4,5,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (4,6,getDate())

INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (5,1,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (5,2,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (5,3,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (5,4,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (5,6,getDate())

INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (6,1,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (6,2,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (6,3,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (6,4,getDate())
INSERT INTO [dbo].[tblFriends] (userID,friendID,dateAdded) VALUES (6,5,getDate())



CREATE TABLE [dbo].[tblHotList](
[UserID] [int] NOT NULL,
[HotUserID] [int] NOT NULL,
[dateAdded] [smalldatetime] NULL
)


INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (1,1,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (1,2,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (1,3,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (1,4,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (1,5,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (1,6,getDate())

INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (2,1,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (2,2,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (2,3,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (2,4,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (2,5,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (2,6,getDate())

INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (3,1,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (3,2,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (3,3,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (3,4,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (3,5,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (3,6,getDate())

INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (4,1,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (4,2,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (4,3,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (4,4,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (4,5,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (4,6,getDate())

INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (5,1,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (5,2,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (5,3,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (5,4,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (5,5,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (5,6,getDate())

INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (6,1,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (6,2,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (6,3,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (6,4,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (6,5,getDate())
INSERT INTO [dbo].[tblHotList] (userID,hotUserID,dateAdded) VALUES (6,6,getDate())




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

INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (1,1,getDate(),'this is the photo caption',1)
INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (2,1,getDate(),'this is the photo caption',1)
INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (3,1,getDate(),'this is the photo caption',1)
INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (4,1,getDate(),'this is the photo caption',1)

INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (1,2,getDate(),'this is the photo caption',1)
INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (2,2,getDate(),'this is the photo caption',1)
INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (3,2,getDate(),'this is the photo caption',1)

INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (1,3,getDate(),'this is the photo caption',1)
INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (2,3,getDate(),'this is the photo caption',1)
INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (3,3,getDate(),'this is the photo caption',1)
INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (4,3,getDate(),'this is the photo caption',1)

INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (1,4,getDate(),'this is the photo caption',1)
INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (2,4,getDate(),'this is the photo caption',1)
INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (3,4,getDate(),'this is the photo caption',1)
INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (4,4,getDate(),'this is the photo caption',1)

INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (1,5,getDate(),'this is the photo caption',1)
INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (2,5,getDate(),'this is the photo caption',1)
INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (3,5,getDate(),'this is the photo caption',1)
INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (4,5,getDate(),'this is the photo caption',1)

INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (1,6,getDate(),'this is the photo caption',1)
INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (2,6,getDate(),'this is the photo caption',1)
INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (3,6,getDate(),'this is the photo caption',1)
INSERT INTO [dbo].[tblExtraPhotos] (photoID,userID,photoDate,caption,status) VALUES (4,6,getDate(),'this is the photo caption',1)






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

INSERT INTO [dbo].[tblUserDetails] (nameOnline,password,date,lastLoggedIn,lastUpdated) VALUES ('user1','password1',getDate(),getDate(),getDate())
INSERT INTO [dbo].[tblUserDetails] (nameOnline,password,date,lastLoggedIn,lastUpdated) VALUES ('user2','password2',getDate(),getDate(),getDate())
INSERT INTO [dbo].[tblUserDetails] (nameOnline,password,date,lastLoggedIn,lastUpdated) VALUES ('user3','password3',getDate(),getDate(),getDate())
INSERT INTO [dbo].[tblUserDetails] (nameOnline,password,date,lastLoggedIn,lastUpdated) VALUES ('user4','password4',getDate(),getDate(),getDate())
INSERT INTO [dbo].[tblUserDetails] (nameOnline,password,date,lastLoggedIn,lastUpdated) VALUES ('user5','password5',getDate(),getDate(),getDate())
INSERT INTO [dbo].[tblUserDetails] (nameOnline,password,date,lastLoggedIn,lastUpdated) VALUES ('user6','password6',getDate(),getDate(),getDate())




CREATE TABLE [dbo].[tblComment](
[CommentID] [int] IDENTITY(1,1) NOT NULL,
[CommentTo] [int] NOT NULL,
[CommentFromID] [int] NULL,
[Comment] [varchar](750) NOT NULL,
[Date] [smalldatetime] NOT NULL,
[Active] [tinyint] NULL,
[IP] [varchar](15) NULL
)


INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (1,2,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (1,3,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (1,4,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (1,5,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (1,6,'hey this is my comment to you',getDate(),1,'192.168.1.1')

INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (2,1,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (2,3,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (2,4,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (2,5,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (2,6,'hey this is my comment to you',getDate(),1,'192.168.1.1')

INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (3,1,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (3,2,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (3,4,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (3,5,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (3,6,'hey this is my comment to you',getDate(),1,'192.168.1.1')

INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (4,1,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (4,2,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (4,3,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (4,5,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (4,6,'hey this is my comment to you',getDate(),1,'192.168.1.1')

INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (5,1,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (5,2,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (5,3,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (5,4,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (5,6,'hey this is my comment to you',getDate(),1,'192.168.1.1')

INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (6,1,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (6,2,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (6,3,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (6,4,'hey this is my comment to you',getDate(),1,'192.168.1.1')
INSERT INTO [dbo].[tblComment] (commentTo,commentFromID,comment,date,active,IP) VALUES (6,5,'hey this is my comment to you',getDate(),1,'192.168.1.1')





CREATE TABLE [dbo].[tblStatusUpdates_PrivacySettings](
[privacyID] [int] IDENTITY(1,1) NOT NULL, --perhaps just get rid of this column as its probably not necessary
[userID] [int] NULL,
[allow_AddedPhoto] [tinyint] NULL,
[allow_AddedFriend] [tinyint] NULL,
[allow_ReceivedComment] [tinyint] NULL,
[lastUpdated] [datetime] NULL
) ON [PRIMARY]




INSERT INTO [dbo].[tblStatusUpdates_PrivacySettings] (userID,allow_AddedPhoto,allow_AddedFriend,allow_ReceivedComment,lastUpdated) VALUES (1,1,1,1,getDate())
INSERT INTO [dbo].[tblStatusUpdates_PrivacySettings] (userID,allow_AddedPhoto,allow_AddedFriend,allow_ReceivedComment,lastUpdated) VALUES (2,0,0,0,getDate())
INSERT INTO [dbo].[tblStatusUpdates_PrivacySettings] (userID,allow_AddedPhoto,allow_AddedFriend,allow_ReceivedComment,lastUpdated) VALUES (3,0,1,0,getDate())
INSERT INTO [dbo].[tblStatusUpdates_PrivacySettings] (userID,allow_AddedPhoto,allow_AddedFriend,allow_ReceivedComment,lastUpdated) VALUES (4,1,0,1,getDate())
INSERT INTO [dbo].[tblStatusUpdates_PrivacySettings] (userID,allow_AddedPhoto,allow_AddedFriend,allow_ReceivedComment,lastUpdated) VALUES (5,0,0,1,getDate())
INSERT INTO [dbo].[tblStatusUpdates_PrivacySettings] (userID,allow_AddedPhoto,allow_AddedFriend,allow_ReceivedComment,lastUpdated) VALUES (6,1,1,0,getDate())
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-03-18 : 16:40:01
Can you post the output based on this input pls.
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-03-18 : 16:47:01
Not sure on the output you want and the relationships bw tables.. But going by the description, are you looking for this ??

select	a.userid,
		a.friendid,
		photo=case when b.allow_addedphoto=0 then 'blocked' else 'allowed' end 
from	[tblFriends] a 
		join [dbo].[tblStatusUpdates_PrivacySettings] b  on a.friendid=b.userid
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2009-03-18 : 18:49:47
Sakets,

I believe your possibly going the right way, I just can't fully vision it properly.

As far as sample output, it will work the following way.

If we execute :

[dbo].[uspGetUserActivityList-test] 1

(this person has a friends relationship with everyone in our tables, so every user would be brought back)
All updates returned, but the following would be filtered


USERID = 1 -> All updates brought back
USERID = 2 - > NO upates would be brought back at all since this user has all privacy settings ON
USERID = 3 -> Only updates for "Received Comments" would be returned, since userID = 3 has all other privacy settings ON
USERID = 4 -> Updates for "Added Friends", and "Added would be returned, since userID = 4 has all other privacy settings ON
USERID = 5 -> Updates for "Received Comments" would be returned, since userID = 5 has all other privacy settings ON
USERID = 6 -> Updates for "Added Photo", and "Added Friend" would be returned, since userID = 6 has Received Comments privacy setting ON



Make sense?



thanks again,
mike123
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2009-03-20 : 16:25:59
bump for a good cause :)


thanks!
mike123
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site