Join with count conditition

Question

Consider the following Schema:

Table Invoices:

Id|TotalSum|Status

1|12|1

1|13|0

Table ClientInvoices:

Id|ClientId|InvoiceId

Table "invoices" may contain multiple invoices with the same Id but with different status (there is unique constraint on Id and status.). Status is integer that represents enum (0 - inactive, 1 - active)

I want to get all the client invoices with price changes if any:

ClientId|InvoiceId|SumBefore|SumAfter

If there is no inactive invoices then PreviousPrice should be null. I am trying to achieve this using the following query:

SELECT Clients.Id AS ClientId,
Invoice.Id AS InvoiceId,
ActualInvoice.TotalSum AS SumBefore,
InActiveInvoice.TotalSum AS SumAfter
FROM Invoices
LEFT JOIN ClientInvoices AS ActualInvoice ON ActualInvoice.InvoiceId AND Status = 1
LEFT JOIN ClientInvoices AS Inactive ON ActualInvoice.InvoiceId AND Status = 0
LEFT JOIN Clients ON Clients.Id = ClientInvoices.ClientId

This works well if there are two records for one invoice: past(inative) and current. However if there is only one invoice - it has active status and using the query above I get SumBefore = null and SumAfter = value which should be in SumBefore column.

It would be great if I could specify to join record on condition if count of row of that record is f.e. 1. Is that possible?

Answer 1

I think this should work:

select c.ClientId as ClientId,
a.InvoiceId as InvoiceId,
a.TotalSum as SumBefore, 
b.TotalSum as SumAfter
from Invoices a left join Invoices b
on a.InvoiceId = b.InvoiceId
and a.status = 1 and b.status = 0
join ClientsInvoices c on c.InvoiceId = a.InvoiceId