Duh Join Problem

[dukeslater]

Someone please point out the error of my ways. I've tried this query several ways with the same results - as soon as I add the second total I get a cartesian join. I'm not seeing why this wouldn't give me each record from the visit table, and total payments and adjustments from the subqueries where there are matching visit numbers. There may be visits with payments and no adjustments, and vice versa.

------------------------------------

select a.visit_no,
qry_pay.payments,
qry_adj.adjustments


from visit a LEFT OUTER JOIN
(select visit_no, sum(pay_amt) as payments
from payments
group by visit_no) qry_pay

on a.visit_no = qry_pay.visit_no

LEFT OUTER JOIN
(select visit_no, sum(adj_amt) as adjustments
from adjustments
group by visit_no) qry_adj

on a.visit_no = qry_pay.visit_no

-------------------------------------

Thanks in advance for your help.

 

[vongrunt]

Both JOIN conditions look the same: (?!?)

on a.visit_no = qry_pay.visit_no

Copy & paste?


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[dukeslater]

Right you are - working perfectly now. I might have looked harder if I hadn't spent two hours on this already. This was the first try - it returns the correct number of records, but as soon as I add the second sum the totals are way high. Can it be done in this manner? Thanks...

select a.visit_no,
sum(b.adj_amt),
sum(c.pay_amt)

from visit a LEFT OUTER JOIN adjustments b
on a.visit_no = b.visit_no

LEFT OUTER JOIN payments c
on a.visit_no = c.visit_no

group by a.visit_no

 

[vongrunt]

I'm afraid not. Suppose that for certain visit_no there are two (2) adjustments and three (3) payments. Because joins don't join on unique column(s) you'll get intermediate Cartesian product (2x3 = 6 rows) and SUM() values will skyrocket.

With derived tables - as used in your original query - or eventually subqueries, there are no such logical problems.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)