Help with getting all records from a table when grouping by other tables

[japeconsulting]

I need to amend the following query so that I get ALL records from the OACT table. Because I am grouping by fields from other tables as there are no records for some of the accounts in this OACT table it is not including all of the accounts. Could someone assist as to how I can modify this query to do this please?

SELECT T0.FatherNum,
T0.FormatCode,
T3.F_RefDate,
T3.SubNum,
T2.FinncPriod,
SUM(T1.Credit-T1.Debit) Balance,
FROM OACT T0

INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account

INNER JOIN OJDT T2 ON T1.TransId = T2.TransId and
T2.FinncPriod>={?Period} and T2.FinncPriod<={?Period2}

INNER JOIN SBOMDEV01_Live.dbFPR T3 ON T2.FinncPriod=T3.AbsEntry

LEFT OUTER JOIN SBOMDEV01_Live.dbBGT T4 ON T0.AcctCode=T4.AcctCode AND T4.Instance=2

WHERE T0.ActType in ('I','E')
GROUP BY
T0.FatherNum,
T0.FormatCode,
T2.FinncPriod,
T3.F_RefDate,
T3.SubNum

Thanks so much.

 

[bborissov]

LEFT JOIN not INNER JOIN.

Borislav Borissov
VFP9 SP2, SQL Server

 

[Olaf Doschke]

Boris has a good point. In short inner joins filter data, while left (or more general outer) joins keep all data from the initial record.

As you group by T2 and T3 data too, you may have to think about using COALESCE() or ISNULL() expressions. For example about the SUM(): Null values are automatically off the summations, that means if one of Credit/Debit always is NULL then Credit-Debit will always be NULL and you don't get a Balance. If one of them always is 0 and the other numeric, you have no problem with NULLs. If some values sometimes are NULL you have the worst case, as that will falsify the SUM and you'll not notice by looking at the result values - only if you proof check a sum.

What you ARE doing correctly is putting the conditions about further tables into join conditions, you might really be okay with mainly turning the INNER to LEFT joins. Id also adjust the SUM to SUM(ISNULL(T1.Credit,0)-ISNULL(T2.Debit,0)) as Balance. Balance just is the wrong word, as you only include the transactions of a certain time span you get the net Credit-Debit of that time span, that is the Balance change within that period. The Balance directly after this period would need to SUM the Balance as it is at start of that period to the Sum(Credit-Debit) of that period. So either you need to Sum back to beginning of each account or you need to name that result a balance change. Anyway you have to be aware of the meaning of that sum.

From accounting point of view the summation of transactions debit and credit of a time period can only give you one aggregated transaction with a total credit or debit, depending on the sign of the sum. A balance always has to take into account (no pun intended) initial balance plus all transactions up to a point, and so a sum of transactions of a given time period only is the accounts full absolute balance, if the balance exactly at start of that time period was 0. Otherwise the aggregation only is a total transaction and should be named so to avoid any wrong interpretation of the query result.

Bye, Olaf.