dimanche 19 avril 2015

Querying three tables at once SQLite

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:



  1. Get ALL COMPANIES

  2. Get the number of ACCOUNTS under a COMPANY

  3. 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?


Aucun commentaire:

Enregistrer un commentaire