JOIN with multiple clauses

1

Hi everyone,

I have a table called CUSTOMERS and another called ACCOUNTS

In the CUSTOMERS table I have a field called ACCNO and it matches with a field in ACCOUNTS called ANUMBER also in this file is a column called UNPAID that contains the amount of unpaid invoices there are for the year.

What Id like to do in English is show the matches where the account number appears in the CUSTOMERS file as well as the ACCOUNTS table where there are more than 4 unpaid invoices.

Any ideas how I can go about this?

2

If I read the question right, I think this should give you what you want.

SELECT C.ACCNO, UnP.UnPaid As UnpaidInvoices
FROM CUSTOMERS C
INNER JOIN
(
SELECT ANUMBER, COUNT(UNPAID) As UnPaid
FROM ACCOUNTS
GROUP BY ANUMBER
HAVING COUNT(UNPAID) > 4
)UnP ON C.ACCNO = UnP.ANUMBER

3

The way I read this, UNPAID is already a counter, so the code to check it should be "SUM(UNPAID)" rather than "COUNT(UNPAID)".