Подзапрос возвращает несколько полей в SQL Server 2008r2

Я делаю некоторые отчеты в Delphi, и мне требуется, чтобы вся информация заголовка счета была возвращена в одном запросе. Проблема в том, что 4 поля, которые мне нужны для заголовка, могут поступать из 4 разных мест в зависимости от значения в главной таблице. У меня есть следующее (что не работает) в качестве примера того, что я пытаюсь сделать:

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) 

Проблема связана с подзапросами в выражении case. У кого-нибудь есть элегантный способ сделать это?

Обновить:

Я действительно придумал следующее, которое, похоже, работает:

 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) 

Но я не уверен, что это будет работать при любых обстоятельствах (я никогда не использовал константы в соединении раньше), или если есть еще лучший способ сделать то, что я хочу.

Вы можете попробовать следующее:

 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 ,COALESCE(H1.VESSELID, H2.VESSELID, H3.VESSELID) AS VESSELID ,COALESCE(H1.VOYAGENUMBER, H2.VOYAGENUMBER, H3.VOYAGENUMBER) AS VOYAGENUMBER ,COALESCE(H1.ARRIVALTIME, H2.ARRIVALTIME, H3.ARRIVALTIME) AS ARRIVALTIME ,COALESCE(H1.DEPARTURETIME, H2.DEPARTURETIME, H3.DEPARTURETIME) AS DEPARTURETIME FROM [TRAN] T INNER JOIN ACCOUNTS A ON (T.ACCOUNTID = A.ID) INNER JOIN [ADDRESS] D ON (T.MAILINGID = D.ID) INNER LEFT JOIN ACCOUNTS B ON (T.BRACCOUNTID = B.ID) INNER JOIN TYPES_LIST TL ON (D.COUNTRY = TL.ID) INNER JOIN CURRENCY C ON (T.ENTEREDCURRENCY = C.ID) LEFT JOIN HMLOG AS H1 ON H1.ID = T.REFERENCESID AND T.ORIGIN = 25 LEFT JOIN CARGO_BOL AS CB INNER JOIN HMLOG AS H2 ON CB.HMLOGID = H2.ID ON CB.ID = T.REFERENCESID AND T.ORIGIN = 26 LEFT JOIN CONTAINERLOG AS CL INNER JOIN HMLOG AS H3 ON CL.HMLOGOUTID = H3.ID ON CL.ID = T.REFERENCESID AND T.ORIGIN = 27 WHERE (T.ID = 269017) 
Давайте будем гением компьютера.