help with query (filtering 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 2005 Forums

Transact-SQL (2005)

help with query (filtering results)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-26 : 20:11:00

Hi,

I have a query as shown below. I am just pasting a portion of the query below, as it will be easier to understand this way. There are actually about 15 tables that we union together, but if I can get it working for just 1 table, then I can copy the logic to all the rest of the tables.

What I am looking to do, is filter results based on a privacy setting. Users that are populated into the intial @Friends TABLE have a privacy setting that allows or disallows other users to select from them.

These settings are stored in a table.

We can select all the privacy settings from the table here:

SELECT
allow_AddedPhoto,allow_AddedFriend,allow_ReceivedComment,allow_LeftComment,allow_LeftPhotoComment,allow_ReceivedPhotoComment,allow_CreatedForumPost,allow_updatedProfile,allow_hasLoggedIn,allow_StartedBattle,allow_AcceptedBattle,allow_hasBeenHonored,allow_updatedStatus

FROM [dbo].[tblStatusUpdates_PrivacySettings] WHERE userID = @userID

Query: For this query below, since it is only 1 table of the 15 or so tables we are querying, the only privacy value that is of importance is @allow_AddedPhoto

This value will control whether for each user in the @Friends TABLE , whether other users can select their updates.

So if user 100 runs this query, and user 200 is in the @Friends TABLE, when we select user 200's latest photos, we must determine user 200's privacy settings. If they are set to 0, or the row does not exist, then user 100 will not bring back any of user 200's photos.

I hope this explanation makes sense ?

Any help is greatly appreciated, just trying to figure out how to add this functionality.

DECLARE @userID INT
DECLARE @topCount tinyint
DECLARE @showMe_AddedPhoto tinyint

SET @userID = 411
SET @topCount = 10
SET @showMe_AddedPhoto = 1

--above lines just filling with variables

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

INSERT @Friends
(
friendID,
nameOnline
)

-- GET FRIENDS TABLE
SELECT f.friendID,
u.nameOnline
FROM tblFriends AS f
INNER JOIN tblUserDetails AS u ON u.userID = f.friendID
WHERE f.userID = @userID AND f.friendID <> @userID

AND f.friendID NOT IN (SELECT [blocked_userID] FROM [dbo].[tblStatusUpdates_BlockedUsers] WHERE userID = @userID)

--end population of who to join to
SELECT TOP (@topCount) *
FROM (

-- Present the resultset #1 ADDED NEW PHOTOS
SELECT nameonline + 'added a new pic' 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, caption,
-- 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

--lets limit it for personal preferences
WHERE @showMe_AddedPhoto = 1

--UNION ALL .......
--about 15 more tables we union on too

) a

ORDER BY theDate DESC

GO

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-03-27 : 05:37:07

Why not just use a join?


--end population of who to join to
SELECT TOP (@topCount) * FROM (
-- Present the resultset #1 ADDED NEW PHOTOS
SELECT nameonline + 'added a new pic' 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, caption, photoDate as maxDate FROM tblExtraPhotos WHERE status = 1 AND userID <> @userID) AS ep 
	ON ep.userID = f.friendID
inner join [dbo].[tblStatusUpdates_PrivacySettings] ps
	on ps.userID = f.friendID
--lets limit it for personal preferences 
WHERE ps.allow_AddedPhoto = 1
--UNION ALL .......
--about 15 more tables we union on too

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-27 : 13:22:48

that may be what I am looking for... testing results now..

thx!

--end population of who to join to
SELECT TOP (@topCount) * FROM (
-- Present the resultset #1 ADDED NEW PHOTOS
SELECT nameonline + 'added a new pic' 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, caption, photoDate as maxDate FROM tblExtraPhotos WHERE status = 1 AND userID <> @userID) AS ep
ON ep.userID = f.friendID
inner join [dbo].[tblStatusUpdates_PrivacySettings] ps
on ps.userID = f.friendID
--lets limit it for personal preferences
WHERE ps.allow_AddedPhoto = 1 AND PS.allow_AddedPhoto = 1
--UNION ALL .......
--about 15 more tables we union on too

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-27 : 13:36:22

what happens if there are no results in the [tblStatusUpdates_PrivacySettings] TABLE ?

I would like the behaviour to be, if the are no results.. it does not filter ... would this be possible ?

thanks again very helpful!

mike123

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-03-27 : 19:38:16

change it to a left join and put the WHERE ps.allow_AddedPhoto = 1 in the join. So you'd do:


INNER JOIN (SELECT userID,counterID, caption, photoDate as maxDate FROM tblExtraPhotos WHERE status = 1 AND userID <> @userID) AS ep 
	ON ep.userID = f.friendID
left join (select * from [dbo].[tblStatusUpdates_PrivacySettings] where allow_AddedPhoto = 1)  ps
	on ps.userID = f.friendID

obviously replacing the * with your required fields.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-27 : 20:39:56

Hey Rick,

I think we are really close, the only thing is, I don't actually need any records from [tblStatusUpdates_PrivacySettings], I am just using this table to filter results. (If allow_AddedPhoto=0 I don't bring back any results)

Should I just change my INNER JOIN to a LEFT JOIN ?

Thanks again much appreciated!
mike123

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-03-30 : 05:08:24

No, because you have the condition in your where clause. You need to at least bring back the userid as this is what you are joining on, so just change my last query to select userid from rather than select * from.

This will then only return those records where allow_AddedPhoto = 1, for allow_AddedPhoto = 0 you will not get a userid returned so there will be nulls in the userid field. It may be worth adding the userid field into you select if you want to check this when the set is returned, therefore making your processing easier. If you do not want to return any records where allow_AddedPhoto = 0, you can just add where ps.userid is not null to the where clause.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources