CODEWITHSUNDEEP
sqlsql-serverdatabase
The_Programmer92
The_Programmer92

Reputation: 79

i want to select both matching and non matching both rows from 2 tables

i have writen this query to select matching and non matching both rows from opening balance table and also from gernalledger table, but its only showing matching rows in two table,i want to select all the rows from openingbalance Table,`

SELECT   GL.AccountId,Ca.Code as Code,CA.AccountName as AccountName,
         SUM(CASE GL.DrCr WHEN 2 THEN ISNULL(Amount,0) END) AS Debit,
         SUM(CASE GL.DrCr WHEN 1 THEN ISNULL(Amount,0) END) AS Credit,
         SUM(CASE o.DrOrCr WHEN 2 THEN ISNULL(o.Balance,0) END) AS OpeningDebit,
         SUM(CASE o.DrOrCr WHEN 1 THEN ISNULL(o.Balance,0) END) AS OpeningCredit
FROM     GeneralLedgerLine GL 
Join ClientAccount CA On GL.AccountId = CA.Id
Left Join OpeningBalance o on o.AccountId=CA.Id
Where CA.ClientId = 1

GROUP BY GL.AccountId,CA.Code,CA.AccountName

`OpeningBalanceTable

Output

Upvotes: 0

Views: 72

Answers (3)

Gowtham
Gowtham

Reputation: 11

If you want to fetch the data from both the tables, use FULL OUTER JOIN instead of LEFT JOIN in your query as below,

Also join OpeningBalance table on o.AccountId with GL.AccountId in GeneralLedgerLine table

SELECT   GL.AccountId,Ca.Code as Code,CA.AccountName as AccountName,
         SUM(CASE GL.DrCr WHEN 2 THEN ISNULL(Amount,0) END) AS Debit,
         SUM(CASE GL.DrCr WHEN 1 THEN ISNULL(Amount,0) END) AS Credit,
         SUM(CASE o.DrOrCr WHEN 2 THEN ISNULL(o.Balance,0) END) AS OpeningDebit,
         SUM(CASE o.DrOrCr WHEN 1 THEN ISNULL(o.Balance,0) END) AS OpeningCredit
FROM     GeneralLedgerLine GL 
Join ClientAccount CA On GL.AccountId = CA.Id
FULL OUTER Join OpeningBalance o on o.AccountId=GL.AccountId
Where CA.ClientId = 1

GROUP BY GL.AccountId,CA.Code,CA.AccountName

If you want to select all the rows from OpeningBalance table but not all the rows from GeneralLedgerLine table, then use RIGHT OUTER JOIN instead of LEFT JOIN in your query,

Also join OpeningBalance table on o.AccountId with GL.AccountId in GeneralLedgerLine table

SELECT   GL.AccountId,Ca.Code as Code,CA.AccountName as AccountName,
         SUM(CASE GL.DrCr WHEN 2 THEN ISNULL(Amount,0) END) AS Debit,
         SUM(CASE GL.DrCr WHEN 1 THEN ISNULL(Amount,0) END) AS Credit,
         SUM(CASE o.DrOrCr WHEN 2 THEN ISNULL(o.Balance,0) END) AS OpeningDebit,
         SUM(CASE o.DrOrCr WHEN 1 THEN ISNULL(o.Balance,0) END) AS OpeningCredit
FROM     GeneralLedgerLine GL 
Join ClientAccount CA On GL.AccountId = CA.Id
RIGHT OUTER Join OpeningBalance o on o.AccountId=GL.AccountId
Where CA.ClientId = 1

GROUP BY GL.AccountId,CA.Code,CA.AccountName

Upvotes: 0

ÖMER HATİP
ÖMER HATİP

Reputation: 126

Try this:

Full Outer Join OpeningBalance o on o.AccountId=CA.Id

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can use left join for three tables and move CA.ClientId = 1 in On Clause instead of Where Clause

SELECT   GL.AccountId,Ca.Code as Code,CA.AccountName as AccountName,
         SUM(CASE GL.DrCr WHEN 2 THEN ISNULL(Amount,0) END) AS Debit,
         SUM(CASE GL.DrCr WHEN 1 THEN ISNULL(Amount,0) END) AS Credit,
         SUM(CASE o.DrOrCr WHEN 2 THEN ISNULL(o.Balance,0) END) AS OpeningDebit,
         SUM(CASE o.DrOrCr WHEN 1 THEN ISNULL(o.Balance,0) END) AS OpeningCredit
FROM     GeneralLedgerLine GL 
LEft Join ClientAccount CA On GL.AccountId = CA.Id
Left Join OpeningBalance o on o.AccountId=CA.Id
and CA.ClientId = 1

GROUP BY GL.AccountId,CA.Code,CA.AccountName

Upvotes: 1

Related Questions