INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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.dbo.OFPR T3 ON T2.FinncPriod=T3.AbsEntry

LEFT OUTER JOIN SBOMDEV01_Live.dbo.OBGT 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.

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

LEFT JOIN not INNER JOIN.

Borislav Borissov
VFP9 SP2, SQL Server

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

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close