score:2

You can look here: http://www.hookedonlinq.com/OuterJoinSample.ashx as an example of the left outer join. And you can always swap tables to get either left or right

score:1

I've taken the liberty of beatifying your TSQL a little.

The last two join conditions appear malformed to me so this TSQL can not be parsed.

SELECT
                  [t].SanadID
                , [t].Date
                , [a].ID [AccID]
                , [a].FullId
                , [a].Name
                , SUM([t].Debit) [TotalDebit]
                , SUM([t].Credit) [TotalCredit]
     FROM
                dbo.FinnAccount [a1] 
         LEFT OUTER JOIN
                dbo.FinnAccount [a]
                    ON [a1].ParentId = [a].ID
         RIGHT OUTER JOIN
                dbo.FinnTrans [t]
         LEFT OUTER JOIN
                dbo.FinnAccount [a2] 
                    ON [a].AccID = [a2].ID 
                    ON [a1].ID = [a2].ParentId
     WHERE 
                 [t].FPID = 7
         AND 
                 [a2].AccLevel = 3
     GROUP BY
                   [t].SanadID
                 , [t].Date
                 , [a].ID
                 , [a].Name
                 , [a].FullId  
         HAVING 
                 [t].SanadID = 1 

Related Articles