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 have 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.