I need some help sorting out my queries, it's the first time I've run into the need to query three tables at once so I'm a little lost.
The database looks like this:
1[COMPANY] --- <has> --- *[ACCOUNTS]1 --- <has> --- *[PAYMENTS]
Table COMPANY has columns: CompID | CompanyName |
Table ACCOUNTS has columns: AccID | Name | Company |
Table PAYMENTS has columns: PayID | GroupID | PaymentDue | DateDue
where Company references CompID and GroupID references AccID.
I need to:
- Get ALL COMPANIES
- Get the number of ACCOUNTS under a COMPANY
- Total all the PAYMENTS of the ACCOUNTS in a COMPANY based on the closest date to the current date.
This is how my display should look:
COMPANY | No. of ACCOUNTS | TOTAL DUE | DATE DUE
'Comp 1' | 3 | 10000 | 4/30/2015
'Comp 2' | 2 | 8000 | 4/30/2015
Doing this one by one is easy enough but getting them all in one go with so many conditions is confusing me. Can anyone give me a general idea of what a query for this case would look like?