CODEWITHSUNDEEP
mysqlsqlleft-join
Hayden Chambers
Hayden Chambers

Reputation: 737

mysql join across three tables

Similar question to another question except that the three tables I have are hierarchical rather than two tables side by side.

The tables i have are

My top level clients table

id | name
1  | alan
2  | bob
3  | charlie

My 2nd level invoices table

id | client_id | paid
1  | 1         | 0
2  | 1         | 1
3  | 2         | 1

And my 3rd level services table

id | invoice_id | amount
1  | 1          | 5.00
2  | 1          | 10.00
3  | 2          | 5.00
4  | 3          | 10.00

And my desired outcome is

id | name    | amount_owing | amount_paid
1  | alan    | 15.00        | 5.00
2  | bob     | null         | 10.00
3  | charlie | null         | null

I can get something that doesnt take into account whether they are paid or not

SELECT
    clients.email,
    jnt.*
FROM clients
LEFT JOIN (
    SELECT 
        invoices.client_id,
        SUM(amount) AS invoice_total_paid
    FROM
        invoices,
        services
    WHERE 
        invoices.id = services.invoice_id 
    GROUP BY
        invoices.client_id
) AS jnt ON clients.id = jnt.client_id

which gives me

id | name    | client_id | invoice_total
1  | alan    | 1         | 20.00
2  | bob     | 2         | 10.00
3  | charlie | 3         | null

but it doesn't divide up whether invoice was paid or not (and the subsequent paid and owing totals). Any help much appreciated

Upvotes: 1

Views: 52

Answers (2)

SunKnight0
SunKnight0

Reputation: 3351

You need something like

...
SUM(IF(paid=1,amount,0)) AS amount_paid,
SUM(IF(paid=0,0,amount)) AS amount_owning
...

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You can get the expected result using conditional aggregation:

SELECT c.id, c.name,
       SUM(CASE WHEN i.paid = 0 THEN s.amount ELSE 0 END) AS amount_owing,
       SUM(CASE WHEN i.paid = 1 THEN s.amount ELSE 0 END) AS amount_paid
FROM clients AS c
LEFT JOIN invoices AS i ON c.id = i.client_id
LEFT JOIN services AS s ON i.id = s.invoice_id
GROUP BY c.id, c.name

Demo here

Upvotes: 3

Related Questions