Single SQL Server Result Set from Query

Question

Hi

Please advise on how to merge two results in to one using SQL Server 2005.

I have the situation where an Account can have up to two Settlement Instructions and this has been modeled like so:

The slim-ed down schema:

Account
---------------------
Id
AccountName
PrimarySettlementId (nullable)
AlternateSettlementId (nullable)


SettlementInstruction
----------------------
Id
Name

The output I want is a single result set with a select statement something along the lines of this which will allow me to construct some java objects in my Spring row mapper:

select
  Account.Id as accountId, 
  Account.AccountName as accountName, 

  s1.Id as primarySettlementId, 
  s1.Name as primarySettlementName, 

  s2.Id as alternateSettlementId, 
  s2.Name as alternateSettlementName

I've tried various things but cannot find a way to get the result set merged in to one where the primary and alternate FK's are not null.

Finally I have searched the forum, but nothing quite seems to fit with what I need.

Answer 1

You could try something like:

SELECT
  a.Id as accountId, 
  a.AccountName as accountName, 
  'Primary' as Settlementtype
  s1.Name as primarySettlementName, 
FROM 
   Account a
INNER JOIN
   SettlementInstruction s1 ON a.PrimarySettlementId = s1.Id
UNION
SELECT
  a.Id as accountId, 
  a.AccountName as accountName, 
  'Alternate' as Settlementtype
  s2.Name as 'AlternateName', 
FROM 
   Account a
INNER JOIN
   SettlementInstruction s2 ON a.AlternateSettlementId = s2.Id

Does that work for you?

This gives you a list of all the account/settlement instructions for those cases where Accounts have a PrimarySettlementId, and then a second list of account/settlement where the alternate is not null. Those where both are NULL will be omitted. Some Account instances might be duplicated (if both ID fields are valid and not null).

Or if not: what exactly is it you're looking for??

Answer 2

You need an outer join.

select
  Account.Id as accountId, 
  Account.AccountName as accountName, 

  s1.Id as primarySettlementId, 
  s1.Name as primarySettlementName, 

  s2.Id as alternateSettlementId, 
  s2.Name as alternateSettlementName
from Account
     left join SettlementInstruction s1 ON s1.Id = Account.PrimarySettlementId
     left join SettlementInstruction s2 ON s2.Id = Account.SecondarySettlementId
where /* ... */