View Learn

USE [MeshedEducationV2]
GO

/****** Object:  View [Common].[LookupDetailView]    Script Date: 18/07/2022 7:10:37 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [Common].[LookupDetailView]
    AS

    SELECT lud.Id AS ParentId,lud.LookUpName AS ParentName,lud.LookUpValue AS ParentValue, lud.LookupDescription AS ParentDescription
,lud.IsActive AS IsParentActive, lud.DisplaySequence AS ParentDisplaySequence,

lud3.Id AS ReferenceParentId  ,lud3.LookUpName AS ReferenceParentName,lud3.LookUpValue AS ReferenceParentValue, lud3.LookupDescription AS ReferenceParentDescription
,lud3.IsActive AS IsReferenceParentActive, lud3.DisplaySequence AS ReferenceParentDisplaySequence,

lud2.Id AS ChildId,lud2.LookUpName AS ChildName,lud2.LookUpValue AS ChildValue, lud2.LookupDescription AS ChildDescription
,lud2.ParentLookUpId
,lud2.IsActive AS IsChildActive,lud2.DisplaySequence AS ChildDisplaySequence, lud2.VetCode AS ChildVetCode
FROM Common.LookUpDetail lud LEFT JOIN Common.LookUpDetail lud2
ON lud.Id = lud2.ParentLookUpId
LEFT JOIN Common.LookUpDetail lud3
ON lud3.Id = lud2.referencelookupid
WHERE lud.ParentLookUpId IS NULL
GO