你可以使用left join，类似于：

select i.*,
       coalesce(mi.code, me.code) as code_1,
       coalesce(mi.description, me.description) as description_1
from item i left join
     miscellaneous mi
     on mi.code = i.key and i.code = 'misc' left join
     medicine me
     on me.code = i.key and i.code = 'med';

您可以尝试使用LEFT JOIN，这将是实现此操作最简单的方法。
使用UNION ALL

SELECT *
FROM item i
INNER JOIN miscellaneous m on m.code=i.code
UNION ALL
SELECT *
FROM item i
INNER JOIN medicine  me on me.code=i.code

使用LEFT JOIN

SELECT *
FROM item i
LEFT JOIN miscellaneous m on m.code=i.code
LEFT JOIN medicine  me on me.code=i.code

由于Item是您'基' /主表，因此您可以使用LEFT JOIN匹配其它表，以便显示Item表中所有行

SELECT * 
FROM Item AS i
LEFT JOIN Miscellaneous AS mi ON (mi.[id] = i.[key] AND i.code = 'misc')
LEFT JOIN Medicine AS me ON (me.[id] = i.[key] AND i.code = 'med');

您还可以使用ISNULL（）函数和一些前提条件来合并Miscellaneous & Medicine表中的列

SELECT i.*
  , ISNULL(mi.id, me.id) AS m_id
  , ISNULL(mi.code, me.code) AS m_code
  , ISNULL(mi.description, me.description) AS m_description
FROM Item AS i
LEFT JOIN Miscellaneous AS mi ON (mi.id = i.[key] AND i.code = 'misc')
LEFT JOIN Medicine AS me ON (me.id = i.[key] AND i.code = 'med');

SqlFiddle演示

您可以动态地执行此操作
1.准备测试数据集

IF (OBJECT_ID('item') IS NULL)
BEGIN 
 CREATE TABLE item (id int, [key] nvarchar(128), code nvarchar(128), 
 description nvarchar(512))
 INSERT INTO item (id, [key] , code, description)
  SELECT 1, 1, 'misc', 'miscellaneous'  UNION ALL
  SELECT 2, 1, 'med', 'medicine'UNION ALL
  SELECT 3, 2, 'test not existing table', 'not_existing_table';
END

IF (OBJECT_ID('miscellaneous') IS NULL)
BEGIN 
 CREATE TABLEe miscellaneous (id int, code nvarchar(128), description 
 nvarchar(512))
 INSERT INTO miscellaneous (id, code, description)
  SELECT 1, 'misc1', 'miscellaneous'  UNION ALL
  SELECT 2, 'misc2', 'miscellaneous_xxx';
END

IF (OBJECT_ID('medicine') IS NULL)
BEGIN 
 CREATE TABLE medicine (id int, code nvarchar(128), description 
 nvarchar(512))
 INSERT INTO medicine (id, code, description)
  SELECT 1, 'medicine1', 'medicine'  UNION ALL
  SELECT 2, 'medicine2', 'medicine_xxx';
END

1. SQL指令码

DECLARE @joins nvarchar(max) ='',@sql NVARCHAR(MAX) = ' SELECT * FROM item';

SELECT @joins =     STUFF((SELECT ( CHAR(13)+CHAR(10) + '  LEFT JOIN ' + 
description + ' ' + description + ' ON ' + description +'.id = item.[key] AND  item.description = ''' + description + '''' )
    FROM item i WHERE ISNULL(description,'') != '' AND
    (select  OBJECT_ID(description) ) IS NOT NULL
    GROUP BY description
    ORDER BY description
    FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'');

    IF(ISNULL(@joins,'') != '' )
    BEGIN
     SET @sql = @sql + @joins;
     EXECUTE sp_executesql @sql;
     PRINT @sql
    END;