Query Question

[Kliot]

I hope I can explain this clearly.

I have an Payments_Applied table that shows how payments have been applied to invoices. I want to be able to do a query that will select all invoices that have been paid by a particular check number, this may be one invoice or many.

This is simple to do and I get a list of invoices. The tricky part is a invoice may have been paid by multiple checks and I also want those records to also be returned.

For example I search for a check and find it has been applied to 3 invoices, I then want to check and see if those 3 invoices have also been paid by any other checks and if they have have include those payments in the result.

Any suggestions on how to do this?

 

[snitin78]

Assuming:
1. the columns in Payments_Applied are called invoice_id and check_number
2. I am searching for a check_number = 1

select a.invoice_id, a.check_number
from Payments_Applied a
where a.check_number = 1
union
select b.invoice_id, b.check_number
from Payments_Applied b where b.invoice_id in (select invoice_id from Payments_Applied where check_number= 1)

 

[Kliot]

Thanks for the help, this is what I was looking for, although it seems that the first part of the union is not necessary. All I really need is:

select b.invoice_id, b.check_number
from Payments_Applied b where b.invoice_id in (select invoice_id from Payments_Applied where check_number= 1)

 

[snitin78]

You are right. I over-engineered it :-D