CODEWITHSUNDEEP
sqlsql-serverjoin
m7d
m7d

Reputation: 736

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

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?

Upvotes: 0

Views: 203

Answers (5)

m7d
m7d

Reputation: 736

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.

Upvotes: 0

shahkalpesh
shahkalpesh

Reputation: 33476

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.

Upvotes: 0

Johnny Lamho
Johnny Lamho

Reputation: 115

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

Upvotes: 1

RC.
RC.

Reputation: 28207

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

Upvotes: 1

Rob Farley
Rob Farley

Reputation: 15849

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

Upvotes: 1

Related Questions