
PRINT 'EXECUTE 024'

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_mclBook_homonyms]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[v_mclBook_homonyms]
GO

CREATE VIEW dbo.v_mclBook_homonyms
AS
SELECT     TOP 100 PERCENT Homonym_Name.NameId AS HomonymNameId, Homonym_Name.FullNameCache AS HomonymFullNameCache, 
                      Homonym_Name.NameCache AS HomonymNameCache, dbo.RelNameQualifier.RelNameQualifier, dbo.Name.NameId, dbo.Name.FullNameCache, 
                      dbo.AuthorTeam.AuthorTeamCache AS Author, dbo.Reference.RefYear AS Year, dbo.Reference.Title, dbo.Reference.InRefFk, 
                      InReference.RefYear AS InYear, InReference.Title AS InTitle
FROM         dbo.Name INNER JOIN
                      dbo.RelName ON dbo.Name.NameId = dbo.RelName.NameFk1 INNER JOIN
                      dbo.RelNameQualifier ON dbo.RelName.RelNameQualifierFk = dbo.RelNameQualifier.RelNameQualifierId INNER JOIN
                      dbo.Name AS Homonym_Name ON dbo.RelName.NameFk2 = Homonym_Name.NameId INNER JOIN
                      dbo.AuthorTeam ON Homonym_Name.AuthorTeamFk = dbo.AuthorTeam.AuthorTeamId INNER JOIN
                      dbo.Reference ON Homonym_Name.NomRefFk = dbo.Reference.RefId LEFT OUTER JOIN
                      dbo.Reference AS InReference ON dbo.Reference.InRefFk = InReference.RefId
WHERE     (dbo.RelName.RelNameQualifierFk = 2)
ORDER BY dbo.Reference.RefYear, dbo.Reference.Title

GO


