Help with a Join Clause

[spons]

select SalesReps.SalesRepId,sum(InvoiceItemCommissions.Amount) + sum(ToolCheckCommissions.Amount) From SalesReps inner join Commissions On Commissions.SalesRepId = SalesReps.SalesRepId inner join InvoiceItemCommissions On Commissions.CommissionId = InvoiceItemCommissions.CommissionId inner join ToolCheckCommissions on ToolCheckCommissions.CommissionId = Commissions.CommissionId where ToolCheckCommissions.Paid= 0 AND InvoiceItemCommissions.Paid=0 group by SalesReps.SalesRepId

I am trying to make this so that in the case InvoiceItemCommissions and ToolCheckCommissions have a record with the same CommissionId but one is paid and the other is not, I will get the sum. Currently if the commissionID is the same and one is paid and one is not, that record will not be returned.. For example

InvoiceItemCommissions
CommissionId=1
Amount=3
Paid=0

ToolCheckCommissions
CommissionId=1
Amount=3
Paid=1

I need this query to return 3 For the Amount Column, Currently it just skips that record becuase of the where clause.

Im kindof newbie with sql as you can tell, so any help would be greatly appreciated

 

[SonOfEmidec1100]

Not exactly sure this is what you want, but this will return the sum unless both are paid

where NOT (ToolCheckCommissions.Paid= 1 AND InvoiceItemCommissions.Paid=1)

 

[spons]

That wont work because If one is Paid it still adds it, therefore returning 6 instead of 3.

 

[spons]

AHA looking through the other posts I found a solution.

select SalesReps.SalesRepId,sum(CASE when InvoiceItemCommissions.Paid = 0 THEN InvoiceItemCommissions.Amount ELSE 0 END) + sum(case when ToolCheckCommissions.Paid = 0 THEN ToolCheckCommissions.Amount ELSE 0 END) From SalesReps inner join Commissions On Commissions.SalesRepId = SalesReps.SalesRepId inner join InvoiceItemCommissions On Commissions.CommissionId = InvoiceItemCommissions.CommissionId inner join ToolCheckCommissions on ToolCheckCommissions.CommissionId = Commissions.CommissionId group by SalesReps.SalesRepId