SQL server Left join 子表并获取表名

Question

假设我们有一个 Instrument 表，其子表 Equity 和 Bond 具有外键 InstrumentId。每个仪器在其中一个子表中都有唯一的记录。

是否可以使用左连接创建一个 View ，以查看所有仪器的列包含存在记录的表名？

SELECT        Instrument.InstrumentId, Instrument.Name, ***CHILD_TABLE_NAME***
FROM          Instrument
LEFT OUTER JOIN
              Equity ON Equity.InstrumentId = Instrument.InstrumentId 
LEFT OUTER JOIN
              Bond ON SBond.InstrumentId = Instrument.InstrumentId 

另一种方法是建立内部连接的并集:

SELECT instrumentId, Name, instrumentType 
FROM
(SELECT Instrument.instrumentId, Name, 'Equity' as instrumentType FROM dbo.Equity inner join Instrument on Instrument.InstrumentId = Equity.InstrumentId
UNION
SELECT Instrument.instrumentId, Name, 'Bond' as instrumentType from dbo.Bond inner join Instrument on Instrument.InstrumentId = Bond.InstrumentId) u

Answer 1

一个选择是像这样在你的连接中包含表名

SELECT i.InstrumentId, 
       i.Name, 
       e.TableEquity,
       b.TableBond
FROM Instrument i 
  LEFT OUTER JOIN (select 'Equity' as TableEquity from Equity) e
    ON i.InstrumentId = e.InstrumentId 
  LEFT OUTER JOIN (select 'Bond' as TableBond from Bond) b
    ON i.InstrumentId = b.InstrumentId

由@sofsntp 编辑:将股票/债券合并到一列中

SELECT i.InstrumentId, 
       i.Name, 
       (ISNULL(e.TableEquity,'') + ISNULL(b.TableBond ,''))
FROM Instrument i 
  LEFT OUTER JOIN (select *, 'Equity' as TableEquity from Equity) e
   ON e.InstrumentId = i.InstrumentId 
 LEFT OUTER JOIN (select *, 'Bond' as TableBond  from StraightBond) b
   ON b.InstrumentId = i.InstrumentId