title: Create indexed view with self join
body: <p>I would like create indexed view (on MSSQL 2008 R2) for task: Get list of players, which can control heroes.</p>
  
  <ul>
  <li>Player (tblPlayer) has 0-N heroes (tblBattleTarget + tblHero).</li>
  <li>Player can be in 0-N clans (tblMembershipPlayer2PlayerClan).</li>
  <li><p>Player can share heroes with other players in same clan (tblHero.Sharing = 2).</p></li>
  <li><p>Hero can be controled by owner or by other players in clans where is owner a member and hero is enabled to share.</p></li>
  </ul>
  
  <p>I created query:</p>
  
  <pre><code>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*/
  </code></pre>
  
  <p>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)</p>
  
  <p>I searched the web &amp; 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?</p>
  
  <p>Thanks!</p>