Query help...

[iSeven]

I have a view that returns a subset of contents available to all users within this system. The view works fine and returns the details it needs to. The stored procedure that is used to query the data for a particular user works fine as well. Below is the view that I'm working with.


CREATE VIEW ProgramContents_view

AS

SELECT Cat.Name as CategoryName, Con.Rank,App.ApplicationID, App.Name as AppName, U.UserID, ul.UserListID, Perm.Name as PermissionName, Con.ContentID, ConType.Name as ContentType, prog.Title as ContentName, con.LastUpdated, '' as URL, '' as Type, '' as FileName, '' as SystemFileName, '' as FileSize FROM tbl_Programs prog
INNER JOIN tbl_Content Con ON Con.ContentID = prog.ContentID
INNER JOIN tbl_ContentType ConType ON ConType.TypeID = Con.TypeID
INNER JOIN tbl_ApplicationContents AppCon ON AppCon.ContentID = Con.ContentID
INNER JOIN tbl_Applications App ON AppCon.ApplicationID = App.ApplicationID
LEFT OUTER JOIN tbl_categories Cat ON Cat.CategoryID = prog.CategoryID
LEFT OUTER JOIN tbl_UserContentPermissions UConPerm ON UConPerm.ContentID = Con.ContentID
LEFT OUTER JOIN tbl_Users U ON U.UserID = UConPerm.UserID
LEFT OUTER JOIN tbl_UserListPermissions UListPerm ON UListPerm.ContentID = Con.ContentID
LEFT OUTER JOIN tbl_UserLists ul ON ul.UserListID = UListPerm.UserListID
LEFT OUTER JOIN tbl_ContentPermissions ConPerm ON ConPerm.ContentID = Con.ContentID
LEFT OUTER JOIN tbl_Permissions Perm ON Perm.PermissionID = ConPerm.PermissionID
WHERE (con.ExpirationDate IS NULL OR con.ExpirationDate>=GETDATE())

Here is the SPROC that is used to query the available contents from the above view for a particular user.

CREATE PROC GetTopLevelContents(
@UserID int,
@Brands nvarchar(1000) = null,
@UserGroup nvarchar(100) = null,
@ApplicationID int
)
AS
-- PULL BACK PROGRAM CONTENT BY USER
SELECT DISTINCT CategoryName, Rank,ApplicationID, AppName, ContentID, ContentType, ContentName, LastUpdated, URL, Type, FileName, SystemFileName, FileSize from ProgramContents_view
WHERE UserID = @UserID AND ContentID IN (SELECT DISTINCT ContentID from ProgramContents_view WHERE ApplicationID = @ApplicationID)
UNION
-- PULL BACK PROGRAM CONTENT BY USERGROUP/BRAND
SELECT DISTINCT CategoryName, Rank,ApplicationID, AppName, ContentID, ContentType, ContentName, LastUpdated, URL, Type, FileName, SystemFileName, FileSize from ProgramContents_view
WHERE CHARINDEX('''' + PermissionName + '''',@Brands)>0
AND ContentID IN (SELECT DISTINCT ContentID from ProgramContents_view WHERE PermissionName = @UserGroup AND ApplicationID = @ApplicationID)
Order by CategoryName, Rank ASC

GO

The problem is I need to pull back content now also based on what are called UserLists. UserLists are lists of users who can also be assigned to content, much like individual users can. I'm having a hard time to figure out how to write an addition to the above SPROC that will also pull back contents from the view above based on if a UserID appears in a UserList that has permissions to view a piece of content.

The view is returning correctly the rows for UserLists assigned to content. Now I just need to figure out how to go through that view and pull back records where a UserID is assigned to a UserListID. The details for the UserList portion of the Db are as follows. If there is more information that I have not provided that could help in discerning how to go about doing this; please just let me know and I will reply with it.

tbl_Users U
tbl_UserListAssociation ULA (ULA.UserID = U.UserID)
tbl_UserLists UL (UL.UserListID = ULA.UserListID)
tbl_UserListPermissions ULP (ULP.UserListID = UL.UserListID)
tbl_Content C (C.ContentID = ULP.ContentID)

Thanks for the help. I'm not a DBA by any means, so this one is really scratching the limit of my knowledge.