One to many relationship and totaling problem

Document ID : KB000056254
Last Modified Date : 14/02/2018
Show Technical Document Details

DBA's and query and reporting users have all experienced totaling problems in tables where one to many relationships exist.

For this example, assume the following database structure:

Master:             Detail:
Key     Amount      Key   Year      Flag
XX      100         XX    01        Y
                    XX    02        Y
YY      100         YY    01        Y
ZZ      100         ZZ    01        Y
The relationship from the Master table to the detail table is known as a "one-to-many" relationship because there can be "many" rows in the detail table matching each key in the Master table.

In this case:
XX     100    joins to 2 records in Detail with   XX       01         Y
                                                  XX       02         Y
The logical record will look like this**:
Master.key       master.amount           detail.key    detail.Year       detail.flag
XX               100                     XX            01                 Y
XX               100                     XX            02                 Y
YY               100                     YY            01                 Y
ZZ               100                     ZZ            01                 Y
** note the two rows returned for the key "XX".

When a user creates a query and specifies a SUM aggregate on AMOUNT column from master, the SQL statement would look like:

SELECT master.key, detail.key, detail.year, detail.flag, SUM(master.amount)
FROM master, detail
WHERE master.key = detail.key
GROUP BY master.key, detail.key, detail.year, detail flag

The result would be the total amount (SUM(master.amount)) of $400. This would be correct results for that query, but not the desired results.

The user is trying to get the SUM from the Master (without the additional logical records" returned once the join is applied. The expected result is $300.

In order to achieve the desired results, the user can create a sub-query. This can be accomplished in CleverPath EUREKA:Reporter in either the Filter (custom override) or a SQL Override.

The subselect allows the user to retrieve a result and then use that result in the selection criteria of another query.

The SQL for the sub-query would look like this:
SELECT SUM(master.amt) FROM master WHERE master.key IN
(SELECT detail.key FROM detail, master WHERE detail.flag = "y" and master.key = detail.key)

What the subselect is doing:
Start with the subselect by itself:

SELECT detail.key FROM detail, master
WHERE detail.flag = "y" and master.key = detail.key

The SQL will return, in this case, all the rows in the detail table and will look like:

Detail.Key
XX
XX
YY
ZZ

Now the first part of the query will run having plugged the answer to the subselect where the subselect was:

SELECT SUM(master.amt) FROM master
WHERE master.key IN ("XX", "XX", "YY", "ZZ")

The query returns the first Master row (XX 100) and sees if the key (XX) is IN the list. XX is in the list so the record is returned. The query picks up the next Master row (YY 100) to see if its key (YY) is IN the list. It is, so it is also returned. And so on. Now the sum will be done only on those rows in the Master table that has 3 rows, not the logical table which combines both Master and Detail and has 4 rows.