EXPERT RESPONSE
Thanks for the splendid example.
You have two unrelated one-to-many relationships, from employee to software,
and from employee to hardware, being combined in a query. The important point
is that while multiple software rows are related to an employee, and multiple
hardware rows are related to an employee, the individual software and hardware
rows are unrelated to each other.
The effects of joining them are easy to see in your example.
The software and hardware counts for each employee are multiplied together.
The sums are inflated by a factor equal to the number of unrelated rows. What has
happened is that every single software row for an employee is matched with every
possible hardware row for that employee. Thus, for each employee you get a
"Cartesian product" of software and hardware rows, resulting in
cross join effects.
Here's one way to solve the problem:
select SalesPerson.empno
, Hcount as [Count Of Hardware]
, Hsum as [Sum Of Hardware]
, Scount as [Count Of Software]
, Ssum as [Sum Of Software]
from (
SalesPerson
inner
join (
select empno
, count(amount) as Scount
, sum(amount) as Ssum
from SoftwareSales
group by empno
) as S
on SalesPerson.empno = S.empno
)
inner
join (
select empno
, count(amount) as Hcount
, sum(amount) as Hsum
from HardwareSales
group by empno
) as H
on SalesPerson.empno = H.empno
This is Microsoft Access, right? When
you paste this query into the SQL View window and save it, Access may
replace the inner parentheses around the subqueries with
square brackets and a period, but the query should still continue to work.
Alternatively, you could save each of the subqueries as a query,
then refer to them in the main query's FROM clause, in effect
using the saved queries the way other databases use VIEWs.
The secret is to join each employee to only one row for software
totals and only one row for hardware totals.
|
