title: Have a sub-query return multiple fields in SQL Server 2008r2
body: <p>I am doing some reporting in Delphi and I require all of the invoice header information to be returned in a single query.  The problem is that 4 fields I need for the header can come from 4 different places depending on a value in the master table.  I have the following (which doesn't work) as an example of what I'm trying to do:</p>
  
  <pre><code>select t.ID, t.TRANSACTIONNUMBER, t.TRANSACTIONDATE,
    a.ABBREVIATION, a.NAME, d.LINE1, d.LINE2, d.LINE3,
    d.CITY, d.[STATE], d.ZIPCODE, tl.NAME COUNTRYNAME,
    b.NAME BROKERNAME, t.ORIGIN, t.TRANSACTIONTYPE,
    t.REFERENCESID, t.POSTED, t.DELETED, t.PROFORMA,
    t.REMARKS, abs(t.AMOUNT) AMOUNT, c.CONVERSIONRATE,
    c.ABBREVIATION CURRENCYABBR,
    case t.ORIGIN 
      when 25 then (select VESSELID, VOYAGENUMBER, ARRIVALTIME, DEPARTURETIME from HMLOG where (ID = t.REFERENCESID))
      when 26 then (select VESSELID, VOYAGENUMBER, ARRIVALTIME, DEPARTURETIME from HMLOG where (ID in (SELECT HMLOGID FROM CARGO_BOL WHERE ID = t.REFERENCESID)))
      when 27 then (select VESSELID, VOYAGENUMBER, ARRIVALTIME, DEPARTURETIME from HMLOG where (ID in (SELECT HMLOGOUTID FROM CONTAINERLOG  WHERE ID = t.REFERENCESID)))
      when 28 then (select null VESSELID, null VOYAGENUMBER, null ARRIVALTIME, null DEPARTURETIME)
    end    
  from [TRAN] t join
       ACCOUNTS a on (t.ACCOUNTID = a.id) join
       [ADDRESS] d on (t.MAILINGID = d.id) left join
       ACCOUNTS b on (t.BRACCOUNTID = b.ID) join
       TYPES_LIST tl on (d.COUNTRY = tl.ID) join
       CURRENCY c on (t.ENTEREDCURRENCY = c.ID)
  where (t.ID = 269017)
  </code></pre>
  
  <p>The issue is with the sub-queries in the case statement.  Does anyone have any elegant way of doing this?</p>
  
  <p>Update:</p>
  
  <p>I actually came up with the following which seems to work:</p>
  
  <pre><code>select t.ID, t.TRANSACTIONNUMBER, t.TRANSACTIONDATE,
    a.ABBREVIATION, a.NAME, d.LINE1, d.LINE2, d.LINE3,
    d.CITY, d.[STATE], d.ZIPCODE, tl.NAME COUNTRYNAME,
    b.NAME BROKERNAME, t.ORIGIN, t.TRANSACTIONTYPE,
    t.REFERENCESID, t.POSTED, t.DELETED, t.PROFORMA,
    t.REMARKS, abs(t.AMOUNT) AMOUNT, c.CONVERSIONRATE,
    c.ABBREVIATION CURRENCYABBR,
    case t.ORIGIN when 25 then har.VESSELID when 26 then hbl.VESSELID when 27 then hcl.VESSELID when 28 then null end VESSELID,
    case t.ORIGIN when 25 then va.VESSELNAME when 26 then vb.VESSELNAME when 27 then vc.VESSELNAME when 28 then null end VESSELNAME,
    case t.ORIGIN when 25 then har.VOYAGENUMBER when 26 then hbl.VOYAGENUMBER when 27 then hcl.VOYAGENUMBER when 28 then null end VOYAGENUMBER,
    case t.ORIGIN when 25 then har.ARRIVALTIME when 26 then hbl.ARRIVALTIME when 27 then hcl.ARRIVALTIME when 28 then null end ARRIVALTIME,
    case t.ORIGIN when 25 then har.DEPARTURETIME when 26 then hbl.DEPARTURETIME when 27 then hcl.DEPARTURETIME when 28 then null end DEPARTURETIME
  from [TRAN] t join
       ACCOUNTS a on (t.ACCOUNTID = a.id) join
       [ADDRESS] d on (t.MAILINGID = d.id) left join
       ACCOUNTS b on (t.BRACCOUNTID = b.ID) join
       TYPES_LIST tl on (d.COUNTRY = tl.ID) join
       CURRENCY c on (t.ENTEREDCURRENCY = c.ID) left join
       HMLOG har on (t.ORIGIN = 25) and (t.REFERENCESID = har.ID) left join
       VESSEL va on (t.ORIGIN = 25) and (har.VESSELID = va.ID) left join
       CARGO_BOL bl on (t.ORIGIN = 26) and (t.REFERENCESID = bl.ID) left join
       HMLOG hbl on (t.ORIGIN = 26) and (bl.HMLOGID = hbl.ID) left join
       VESSEL vb on (t.ORIGIN = 26) and (hbl.VESSELID = vb.ID) left join
       CONTAINERLOG cl on (t.ORIGIN = 27) and (t.REFERENCESID = cl.ID) left join
       HMLOG hcl on (t.ORIGIN = 27) and (cl.HMLOGOUTID = hcl.ID) left join
       VESSEL vc on (t.ORIGIN = 27) and (hcl.VESSELID = vc.ID) 
  where (t.ID = 269017)
  </code></pre>
  
  <p>But I'm unsure if this will work under all circumstances (I've never used constants in a join before) or if there is still a better way to do what I want.</p>