Использование Coalesce с несколькими объединениями

Попробуйте выполнить запрос ниже. Это приведет к появлению нескольких записей для одного и того же имени «Джон» и «Боб», поскольку у вас есть несколько внутренних и внешних ролей для того же ИД (Id 3). Если это не то, что вы хотите, сообщите мне, какой из них вы хотите отобразить.

CREATE TABLE #Personnel
    (
        Name VARCHAR(100) ,
        InternalRoleID INT ,
        ExternalRoleID INT
    );

CREATE TABLE #InternalRoles
    (
        ID INT ,
        Name VARCHAR(100)
    );

CREATE TABLE #ExternalRoles
    (
        ID INT ,
        NAme VARCHAR(100)
    );

INSERT INTO #Personnel
VALUES ( 'John', 3, NULL ) ,
       ( 'Bob', NULL, 3 ) ,
       ( 'Rick', 5, NULL ) ,
       ( 'Anna', 6, NULL );


INSERT INTO #InternalRoles ( ID ,
                             Name )
VALUES ( 3, 'Accountant' ) ,
       ( 3, 'Manager' ) ,
       ( 5, 'Supervisor' ) ,
       ( 6, 'Worker' );



INSERT INTO #ExternalRoles ( ID ,
                             NAme )
VALUES ( 3, 'Delivery Driver' ) ,
       ( 3, 'Cleaner' ) ,
       ( 5, 'Electrician' ) ,
       ( 6, 'Auditor' );


SELECT P.Name ,
       COALESCE(IR.Name, ER.NAme) AS RoleName
FROM   #Personnel P
       LEFT JOIN #InternalRoles IR ON P.InternalRoleID = IR.ID
       LEFT JOIN #ExternalRoles ER ON P.ExternalRoleID = ER.ID;

DROP TABLE #ExternalRoles;
DROP TABLE #Personnel;
DROP TABLE #InternalRoles;

Попробуйте следующее: - с использованием Case

SELECT p.Name, CASE WHEN isnull(i.Name,'') = '' THEN e.Name ELSE i.Name END 
  FROM Personnel p LEFT JOIN InternalRoles i ON p.InternalRoleID = i.ID 
               LEFT JOIN ExternalRoles e ON p.ExternalRoleID = e.ID 

Есть несколько способов справиться с этим:

• Используйте COALESCE для возврата первого значения не NULL ( SQL Fiddle )

  SELECT Personnel.Name, COALESCE(InternalRoles.Name, ExternalRoles.Name, 'Unknown') AS RoleName
  FROM Personnel
  LEFT OUTER JOIN InternalRoles ON InternalRoles.ID = Personnel.InternalRoleID AND Personnel.ExternalRoleID IS NULL
  LEFT OUTER JOIN ExternalRoles ON ExternalRoles.ID = Personnel.ExternalRoleID AND Personnel.InternalRoleID IS NULL

• Используйте ISNULL для возврата второго значения, если заданное значение NULL ( SQL Fiddle )

  SELECT Personnel.Name, ISNULL(InternalRoles.Name, ExternalRoles.Name) AS RoleName
  FROM Personnel
  LEFT OUTER JOIN InternalRoles ON InternalRoles.ID = Personnel.InternalRoleID AND Personnel.ExternalRoleID IS NULL
  LEFT OUTER JOIN ExternalRoles ON ExternalRoles.ID = Personnel.ExternalRoleID AND Personnel.InternalRoleID IS NULL

• Используйте CASE для определения поведения if-then-else ( SQL Fiddle )

  SELECT Personnel.Name, CASE WHEN Personnel.InternalRoleID IS NOT NULL THEN InternalRoles.Name ELSE ExternalRoles.Name END AS RoleName
  FROM Personnel
  LEFT OUTER JOIN InternalRoles ON InternalRoles.ID = Personnel.InternalRoleID AND Personnel.ExternalRoleID IS NULL
  LEFT OUTER JOIN ExternalRoles ON ExternalRoles.ID = Personnel.ExternalRoleID AND Personnel.InternalRoleID IS NULL

Реально, единственное, что вам нужно будет сделать, - это то, что лучше всего подходит стиль вашего запроса, так как все они имеют одинаковые планы выполнения и относительно одинаковое время выполнения. Лично я бы рекомендовал использовать COALESCE, так как он позволяет отображать значение по умолчанию, если одно из значений Name в ваших таблицах Internal / ExternalRoles оказывается NULL, если вы этого хотите.

В качестве альтернативы вы можете попробовать следующее:

select [Name],
       coalesce((select [Name] from InternalRoles where id = p.InternalRoleId),
                (select [Name] from ExternalRoles where id = p.ExternalRoleId)) [RoleNamE]
from Personnel p