I want to create an indexed view that integrates data from several tables, however SQL Server complains with the error message:
Cannot create index on view "MyView". The view contains a self join on "dbo.Companies".
The view definition is something like this (simplified version):
SELECT T.Field1
, T.Field2
, P.CompanyName AS ProviderName
, C.CompanyName AS CustomerName
, T3.Field1
FROM dbo.Table1 T
INNER JOIN dbo.Companies P ON T.ProviderId = T2.Id
INNER JOIN dbo.Companies C ON T.CustomerId = T2.Id
INNER JOIN dbo.Table3 ON T.Id = T3.Id
Is there any workaround for this case? I don't want to split the Companies table in two tables.
Thanks in advance.