title: Sql join a table if rows exists otherwise switch to another table
body: <p>considering we have the following query : </p>
  
  <pre><code>SELECT ca.Uid Numb,
         ag.Code Cd,
         ag.Name agn,
         ca.TypeId tt
  FROM dbo.Event ce
      OUTERAPPLY
  (SELECT*FROM dbo.Table1 tca WHERE tca.Uid= ce.Cuid) ca
      OUTERAPPLY
  (SELECT*FROM dbo.Table2 tct WHERE tct.Uid= ca.TypeId) ct
      OUTERAPPLY
  (SELECT*FROM dbo.Table3 ta WHERE ta.Id = ca.AgId) ag
      OUTERAPPLY
  (SELECT*FROM dbo.Table4 tsa WHERE tsa.Uid= ce.SId) ser
      OUTERAPPLY
  (
      --- if Rows exists in this table take it 
      SELECT *
      FROM dbo.Table5 ttra
      WHERE ttra.ReferenceId = ce.TransactionRef
      --- Otherwise use this table
      SELECT *
      FROM dbo.Table6 ttra
      WHERE ttra.ReferenceId = ce.TransactionRef
  ) trx
  WHERE trx.ApprovalDateTime ISNOTNULL
  </code></pre>
  
  <p>what i want basically on the outer apply to takes a table5 if rows exists in it using the where <code>ttra.ReferenceId = ce.TransactionRef</code>
  else use Table6 using the same condition</p>