V_SITEID_MAPFROM_DESIGNATIONID

    create view dbo.V_SITEID_MAPFROM_DESIGNATIONID as 

      select DESIGNATION.ID as DESIGNATIONID, coalesce(DL5.SITEID, DL4.SITEID, DL3.SITEID, DL2.SITEID, DL1.SITEID) as SITEID
            from dbo.DESIGNATION (nolock)
                left join dbo.DESIGNATIONLEVEL DL1 (nolock) on DL1.ID = DESIGNATION.DESIGNATIONLEVEL1ID
                left join dbo.DESIGNATIONLEVEL DL2 (nolock) on DL2.ID = DESIGNATION.DESIGNATIONLEVEL2ID
                left join dbo.DESIGNATIONLEVEL DL3 (nolock) on DL3.ID = DESIGNATION.DESIGNATIONLEVEL3ID
                left join dbo.DESIGNATIONLEVEL DL4 (nolock) on DL4.ID = DESIGNATION.DESIGNATIONLEVEL4ID
                left join dbo.DESIGNATIONLEVEL DL5 (nolock) on DL5.ID = DESIGNATION.DESIGNATIONLEVEL5ID