Getting Multiple rows from 1 table and 1 from another. 1

[Gatorajc]

I have two tables

A customer table

CustID FNAME LNAME Credit
1561 Jane Doe 30
5468 John Doe null
1657 John Smith null

And an Orders

CustID OrderID Orderpaid ordertotal
1561 156445 22 22
1561 156678 null null

This is the result I want to get.

TotalCredit
30

But this is the result I am getting

TotalCredit
60

Since there are 2 rows in the orders table Its counting the customer table twice. How would I get it to count the customer credit once even there is more than 1 order.

This is my query.

(Select sum(ISNULL(c.credit,0) + (ISNULL(o.paidtotal,0) - ISNULL(o.ordertotal,0)) ) as TotalCredit
FROM orders o join customers c on o.customerid = c.customerid
WHERE ((o.PaidTotal > rderTotal and o.CreditFlag is null) or c.Credit > 0) and C.CustomerID = 'W20618')

AJ


If at first you do not succeed, cheat!

 

[Golom]

Try

(Select (sum(ISNULL(c.credit,0)) + 
         sum(ISNULL(o.paidtotal,0)) - 
         Sum(ISNULL(o.ordertotal,0)) ) as TotalCredit

FROM orders o join customers c on o.customerid = c.customerid

WHERE ((o.PaidTotal > o.OrderTotal and o.CreditFlag is null) or c.Credit > 0) and C.CustomerID = 'W20618')

GROUP BY o.customerid

 

[dnoeth]

Just use MIN or MAX instead of SUM on credit:

MAX(ISNULL(c.credit,0)) +
SUM(ISNULL(o.paidtotal,0) - ISNULL(o.ordertotal,0)) as TotalCredit

Dieter

 

[Gatorajc]

Thanks Dieter that worked.

AJ


If at first you do not succeed, cheat!