WITH sea AS (

SELECT

   [CommandSet_ID]     AS "Search_ID"

 , [Name]                    AS "Name"

 , 'CommandSet'           AS "Kind"

 , [Executable_ID]        AS "Excutable_ID"

FROM

 [dbo].[CommandSet]

UNION

SELECT

   [Executable_ID]

 , [Name]

 , 'Executable'

 , [Executable_ID]

FROM

 [dbo].[Executable]

)

SELECT

   sea.[Search_ID]              AS "Primary_ID"

 , sea.[Kind]                      AS "PrimaryKind"

 , sea.[Name]                    AS "PrimaryName"

 , exk.[Name]                    AS "ExecutableKind"

 , exe.[Executable_ID]        AS "Executable_ID"

 , exe.[Name]                    AS "Name"

FROM

 sea

INNER JOIN

 [dbo].[Executable] exe

ON

 exe.[Executable_ID] = sea.[Excutable_ID]

INNER JOIN

 [mgmt].[ExecutableKind] exk

ON

 exk.[ExecutableKind_ID] = exe.[ExecutableKind_ID]

WHERE

 sea.[Search_ID] LIKE '%6BC67EAE%'