I would like to create an indexed view (in MSSQL 2008 R2) for the task: Get a list of players who can control the characters.
- The player (tblPlayer) has 0-N heroes (tblBattleTarget + tblHero).
- The player can be in clans 0-N (tblMembershipPlayer2PlayerClan).
A player can share heroes with other players in the same clan (tblHero.Sharing = 2).
The hero can be controlled by the owner or other players in the clans, where the owner is a member and the hero is included for sharing.
I created a 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 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 in the view, I get the error: Unable to create the index in the view "mydatabase.dbo.vwHero_SharingWithClan". The view contains a self-join to "mydatabase.dbo.tblMembershipPlayer2PlayerClan". (Microsoft SQL Server, Error: 1947)
I searched the books on the Internet and SQL Server on the Internet, and I did not find a way to solve the problem of self-join. Is there any way how to do this? Or how can I rewrite a query for the correct results?
Thanks!
source share