title: SQL server Left join with child tables and get the table name
body: <p>Let's assume we have a table Instrument with child tables Equity and Bond having a foreign key InstrumentId. Each instrument has a unique record in one of the child tables.</p>
  
  <p>Is it possible to make a view using left joins to see all the instrument with a column containing the table name in which the record is present ?</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>An alternative is to make an union of inner joins:</p>
  
  <pre><code>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
  </code></pre>