How to return only rows that are inner-join'd more than once

Question

I have two tables, one for invoices and one for incoming payments. An incoming payment can be joined to an invoice by a foreign key like so:

from invoices t1 inner join incoming_payments t2 on t1.receiptnum = t2.docnum

The question: I want to return all invoices which have more than one payment posted against them. For each invoice I want to return its docnum, which is just a unique id for the invoice.

Here are some things I tried which did not work:

select t0.docnum
from invoices t0 inner join incoming_payments t1 on t0.receiptnum = t1.docentry 
group by t0.docnum
having count(t0.docnum) > 1

and

select t0.docnum
from invoices t0 inner join incoming_payments t1 on t0.receiptnum = t1.docentry
group by t0.receiptnum, t0.docnum
having count(t0.receiptnum) > 1

Any ideas?

Answer 1

I'm not sure why the first one didn't work... It really should've. Can you expand on "it didn't work"?

Try:

select t0.docnum, *
from invoices t0 
  inner join incoming_payments t1 on t0.receiptnum = t1.docentry

And

select t0.docnum, count(*)
from invoices t0 
  inner join incoming_payments t1 on t0.receiptnum = t1.docentry 
group by t0.docnum

to help you figure out what's going on.

Rob

Answer 2

select t0.docnum, count(*)
from invoices t0 inner join incoming_payments t1 on t0.receiptnum = t1.docentry 
group by t0.docnum
having count(*) > 1

Answer 3

What SQL error do you get? Did you group by all fields that were required to be grouped?

Answer 4

I think your example SQL is inconsistent (when compared with the 1st SQL you provided).
i.e. in 1st SQL you are using docnum for comparison and in subsequent SQL, you are using docentry.

select t0.docnum, count(t1.*)
from invoices t0 inner join incoming_payments t1 on t0.receiptnum = t1.docentry
group by t0.docnum
having count(t1.*) > 1

EDIT: What is the matching field for comparison?
t0.receiptnum = t1.docnum OR t0.receiptnum = t1.docentry?

EDIT2: See the modified SQL above.

Answer 5

The answer ended up being much more complex given the schema I was working with. I am not including the actual code here, but I solved the problem a while ago. Just wanted to follow up and thank everyone for helping.