Edit Question - KnowledgeHub

Title Body

I would like create indexed view (on MSSQL 2008 R2) for task: Get list of players, which can control heroes. Player (tblPlayer) has 0-N heroes (tblBattleTarget + tblHero). Player can be in 0-N clans (tblMembershipPlayer2PlayerClan). Player can share heroes with other players in same clan (tblHero.Sharing = 2). Hero can be controled by owner or by other players in clans where is owner a member and hero is enabled to share. I created query: SELECT H.HeroID /*PK of hero*/ , BT.IDBattleTargetOwner /*ID of owner of hero (player)*/ , MP2PC_Other.IDPlayer AS IDOtherPlayerByClan /*ID of another player, which can control hero*/ FROM [dbo].[tblPlayer] AS P_Owner /*owner of heroes*/ INNER JOIN [dbo].[tblBattleTarget] AS BT /*"base class" for hero*/ ON BT.IDBattleTargetOwner = P_Owner.PlayerID INNER JOIN [dbo].[tblHero] AS H /*hero in game*/ ON H.HeroID = BT.BattleTargetID INNER JOIN [dbo].[tblMembershipPlayer2PlayerClan] AS MP2PC_Owner /*hero's owner can be in 0-N clans*/ ON MP2PC_Owner.IDPlayer = BT.IDBattleTargetOwner INNER JOIN [dbo].[tblMembershipPlayer2PlayerClan] AS MP2PC_Other /*other players can be in 0-N clans*/ ON MP2PC_Other.IDPlayerClan = MP2PC_Owner.IDPlayerClan WHERE H.Sharing = [dbo].[CONST_Sharing_PlayerClan]() /*only heroes shared with clan can be in result*/ However when I try to create an index on the view, I get error: Cannot create index on view "mydatabase.dbo.vwHero_SharingWithClan". The view contains a self join on "mydatabase.dbo.tblMembershipPlayer2PlayerClan". (Microsoft SQL Server, Error: 1947) I searched the web & sql server books online and I didn't found way how to workaround the self-join issue. Is there some way how to do that? Or how should I rewrite the query for proper results? Thanks!

Tags (comma-separated) Cancel