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.
