CODEWITHSUNDEEP
sqlsql-serversql-server-2008
DadTo2
DadTo2

Reputation: 69

Duplicate Records in a SQL Join

I have three tables in a SQL Server database (2008 R2): Bidders, Sales, and Transactions.

I have six bidders who have a sale or sales and there are a total of six sales. In the Transaction table are a total of six records. The data is as follows:

Bidder Table Data:

+-----------+-------------------------+
| Bidder ID |       Bidder Name       |
+-----------+-------------------------+
|       101 | Tom & Joan Bergland     |
|       103 | Jack & Sally Cook       |
|       106 | Steve & Jan Cross       |
|       109 | Cynthia Davis           |
|       122 | Arnold & Peggy Jensen   |
|       126 | Wayne & Connie Lindell  |
+-----------+-------------------------+

Sales Table Data:

+---------------+--------------+-------------+------------+
| Sales Counter | BidderNumber | AmountSpent | Quantity   |
+---------------+--------------+-------------+------------+
|             1 |          101 | 2600.00     |        1   |
|             2 |          106 | 90.00       |        1   |
|             3 |          122 | 65.00       |        1   |
|             4 |          103 | 353.00      |        2   |
|             5 |          126 | 2500.00     |        1   |
|             6 |          109 | 315.00      |        3   |
+---------------+--------------+-------------+------------+

Transaction Table Data:

+--------------+------------+------------------+
| BidderNumber | AmountPaid | SalesCounter     |
+--------------+------------+------------------+
|          101 | 2600.00    |              1   |
|          103 | 500.00     |              4   |
|          103 | 206.00     |              4   |
|          122 | 65.00      |              3   |
|          126 | 1500.00    |              5   |
|          126 | 1000.00    |              5   |
+--------------+------------+------------------+

Here is the SQL code I am using to get the data I am looking for:

Select s.[BidderNumber] as 'Bid #',
   ltrim(rtrim(b.bidderName)) as 'Name',
    isnull(sum(s.saleprice * Quantity),0) as 'Spent', 
   isnull(sum(t.Amount),0) as 'Paid', 
   case 
        when sum(t.Amount) is null then sum(s.saleprice * Quantity) 
        else
            case when (sum(t.Amount) > sum(s.saleprice * Quantity)) then (sum(t.Amount) - sum(s.saleprice * Quantity)) 
                 else (sum(s.saleprice * Quantity) - sum(t.amount ))
                 end
        end as 'Outstanding', b.cconfile, t.Notes --, s.biddernumber 
Into #Temp1
from sales s inner join Bidders b on s.BidderNumber = b.BidderNumber
             left  join transactions t on s.SaleCounter = t.SalesCounter  

group by s.BidderNumber, b.bidderName,b.CCOnFile, t.Notes  
order by s.Biddernumber, b.biddername, b.cconfile, t.Notes

  Select [Bid #], Name, Spent as 'Total Purchases', Paid as 'Current Payments', 
       case
            when CCOnFile = 1 then Sum(Outstanding)
           else
                case 
                        when CCOnFile = 0 then 0
                    end
            end as 'Amount To Charge Credit Card',
        case
            when CCOnFile = 0 then sum(Outstanding)
            ELSE
                case
                    when CCOnfile = 1 then 0
                    end
            end as 'Outstanding Balance',
            isnull(Notes,' ')as 'Notes'             
from #Temp1 
group by [Bid #], name, spent, paid,CCOnFile,Notes
order by [Bid #], Name, spent, paid, CCOnFile,Notes


drop table #temp1

Here are the results I am getting back:

Bid # Name Total Purchases Current Payments Amount To Charge Credit Card Outstanding Balance Notes 101 Tom & Joan Bergland 2600.00 2600.00 0.00 0.00
103 Jack & Sally Cook 1412.00 706.00 0.00 706.00
106 Steve & Jan Cross 90.00 0.00 0.00 90.00
109 Cynthia Davis 945.00 0.00 0.00 945.00
122 Arnold & Peggy Jensen 65.00 65.00 0.00 0.00
126 Wayne & Connie Lindell 5000.00 2500.00 0.00 2500.00

My question is simply this: why are the total purchases being doubled? And the doubled totals are ONLY happening when there are two records in the transaction table (see ids number 103 and 126.

Granted, SQL is not my strongest skill, but I think I have this coded correctly. But after 3 days of banging my head, I could really use some advice.

Thanks!!

Upvotes: 0

Views: 117

Answers (1)

Vu cuong
Vu cuong

Reputation: 126

Take a look at sale table: 4 103 353.00 2
and the transaction table: 103 500.00 4
103 206.00 4

When you left join sales and transactions, because the key to join have 2 rows in transactions table so the result will be duplicated, see the join result below:
4 103 353.00 2 103 500.00 4
4 103 353.00 2 103 206.00 4

==> Total Purchases = 353 * 2 + 353*2 = 1412.00


In your sql, instead of left join transactions you should move the case to second select statement and move the calculate pad to sub select like this 

Select s.[BidderNumber] as 'Bid #',
    ltrim(rtrim(b.bidderName)) as 'Name',
    isnull(sum(s.saleprice * Quantity),0) as 'Spent', 
    (select isnull(sum(t.Amount),0) from transactions t
          where t.SalesCounter = s.SaleCounter) as 'Paid', b.cconfile, t.Notes --, s.biddernumber 
Into #Temp1
from sales s inner join Bidders b on s.BidderNumber = b.BidderNumber
group by s.BidderNumber, b.bidderName, b.CCOnFile
order by s.Biddernumber, b.biddername, b.cconfile

Upvotes: 1

Related Questions