SQL Left Join doubling value of summed columns

I've got 2 tables that I need query and return a result set of "exceptions" based upon if the sum of a field in table1 equals the sum of the field in table2 (where other columns match).

select A.TranName,A.TranDate,A.TranCode,SUM(A.TranQty) AS T1Qty,B.TranName,B.TranDate,B.TranCode,SUM(B.TranQty) AS T2Qty
from Table1 AS A
LEFT JOIN Table2 AS B
on A.TranName = B.TranName AND A.TranDate = B.TranDate AND A.TranCode = B.TranCode
GROUP BY A.TranName, A.TranDate, A.TranCode, B.TranName, B.TranDate, B.TranCode
HAVING SUM(A.TranQty)开发者_Go百科 != SUM(B.TranQty)

The result set isn't correct because it multiplies the Table1.TranQty sum by the number of rows returned from Table2.

For example if Table1 had 1 record where the join matched 2 records in Table2, the TranQty from the 1 record in Table1 would be multiplied by 2 (and thus matched incorrectly).

I'm sure I'm missing something basic about using an aggregate function (sum) in a left join.

Thanks for the help!

(System is MSSql)

Try this query..Basically, you should aggregate A's and B's results seperately and verify the counts after that.

select a.TranName,a.TranDate,a.TranCode
from (
select TranName,TranDate,TranCode, SUM(TranQty) AS T1Qty
Table1 
group by TranName,TranDate,TranCode) a
LEFT JOIN
(
select TranName,TranDate,TranCode, SUM(TranQty) AS T1Qty
Table2 
group by TranName,TranDate,TranCode) b
on (A.TranName = B.TranName AND A.TranDate = B.TranDate AND 
    A.TranCode = B.TranCode)
where a.T1Qty != b.T1Qty

Make sure the combination of these three columns is enough to define a row in A and B. If there are additional rows, you might need to add them too.

It's doing exactly what you asked it to do (but that's something other than what you expected it to do). Each row from table 1 will be repeated at least once in the result set. If it matches more than 1 row in table 2, then it will show up that many times.

In looking at your original SQL, it would appear that what you're trying to accomplish is this:

select *
from      ( select TranName ,
                   TranDate ,
                   TranCode ,
                   TranQTy = sum(TranQty)
            from Table1
            group by TranName ,
                     TranDate ,
                     TranCode
          ) A
full join ( select TranName ,
                   TranDate ,
                   TranCode ,
                   TranQTy = sum(TranQty)
            from Table2
            group by TranName ,
                     TranDate ,
                     TranCode
          ) B
where (    A.TranQty is     null and B.TranQty is not null
        OR A.TranQty is not null and B.TranQty is     null
        OR A.TranQt != B.TranQty
      )

You want to find the difference between two sets that are each summaries.