Three different tables query in SQL

Question

table: companies

ID | companyName  
................................  
1  | company1  
2  | company2  
3  | company3  
4  | company4

table:invoice

ID | companyID  
................................  
1  | 1  
2  | 1  
3  | 3  
4  | 2

table: invoiceinput

ID | invoiceID | inputTotal  
................................  
1  | 1  | 100  
2  | 1  | 200  
3  | 1  | 123  
4  | 3  | 211

the field total is

to describe: I have base of companies in table "companies", invoices (in table "invoice") note that several invoices can be applied to one company, and content of invoice (in invoiceinput table). several invoiceinput item can be aplied to one invoice. there is foreign key on invoice.companyID to companies.ID, and on invoiceinput.invoiceID to invoice.ID.

so I want result like this:

companyName | sum of totals
...............................................
company1 | 423
company2 | 0
company3 | 0
company4 | 211

So sum of totals column should contain data that is sum of all totals that are in all invoices which is made for a certain company.

I hope I described it well.

Are you sure company 3 has 0 sum?It seems to me you mistook company4 for company3. — Mihai, Apr 24 '14 at 19:00
This is a rather basic `join` query with a `group by`. What have you tried? — Gordon Linoff, Apr 24 '14 at 19:01
@GordonLinoff I've tried both join and group by also I tried: SELECT companies.category, companies.companyName, ( SELECT SUM(invoiceinput.total) FROM invoiceinput WHERE invoice.companiesID=51 JOIN invoice ON invoice.ID=invoiceinput.invoiceID) - (SELECT SUM(income.income) FROM income) AS debt From companies I've tried much more things than that but nothing worked for me :( — funny, Apr 24 '14 at 19:08
http://stackoverflow.com/questions/1242121/sql-join-group-by-on-three-tables-to-get-totals — Lasitha Benaragama, Apr 24 '14 at 19:09

score 1 · Accepted Answer · answered Apr 24 '14 at 19:18

Try this:

For MySQL:

SELECT Com.CompanyName,IFNULL(SUM(InvIn.InputTotal),0) as SumOfTotal
FROM companies Com LEFT JOIN
Invoice Inv ON Inv.CompanyID=Com.ID LEFT JOIN
InvoiceInput InvIn ON InvIn.InvoiceID=Inv.ID
GROUP BY Com.CompanyName
ORDER BY Com.CompanyName

For SQL Server, replace IFNULL with ISNULL.

Result:

COMPANYNAME   SUMOFTOTAL
company1      423
company2      0
company3      211
company4      0

See result in SQL Fiddle.

Three different tables query in SQL

1 Answers1