MySQL left join with multiple tables and where clauses

Question

I have tried so many combinations so maybe I'm just simply doing it wrong. Well, I definitely am doing it wrong.

I know that code is always requested on Stack Overflow but it'll confuse the question as my code has now mutated into another language.

Confusion:

There's two WHERE clauses and no matter where I place it, I either get an error or the JOIN doesn't work

Problem:

I have 4 tables:

customers - I need ALL the rows returned even if there's no payment to it but only if customer_status = 1

payments - contains customer_id linked to customers table - I need the SUM returned from payment_amount only if payment_status = 1

branches - contains customer_id linked to customers table

managers - contains branch_id linked to branches table

CUSTOMER    |    TOTAL_RECEIVED    |    BRANCH      |    MANAGER_NAME
----------------------------------------------------------------------
Pepsi       |    £1000             |    London      |    Mr Smith
Coca Cola   |                      |    Manchester  |    Mr Beckham
Dr Pepper   |    £2500             |    Shanghai    |    Mr Miagi
Duff        |                      |    Springfield |    Mr Simpson

As you can see, coca cola and duff haven't paid (no record in payment table) but it should still list

So customers should all show up if customer_status = 1
SUM of payment only if it's in database and payment_status = 1
Branch and Manager should list for that customer as it's always in database
Managers is in its own table as there's many and assigned to different branches at different times (as I have a feeling someone might ask)

Fields selected and where summary

c.customer_id, c.customer_companyname, c.customer_status FROM customers c WHERE c.customer_status = 1

SUM(p.payment_amount) as total_received, p.customer_id, p.payment_status FROM payments p WHERE p.payment_status = 1

b.branch_id, b.branch, b.customer_id FROM branches b WHERE b.customer_id = c.customer_id

m.manager_id, m.manager_name, m.branch_id FROM managers m WHERE m.branch_id = b.branch_id

Currently seeing this solution as it's similar but not the same http://stackoverflow.com/questions/4704095/mysql-left-join-on-multiple-tables-with-conditions - will post solution is figured — Onimusha, Jun 03 '14 at 22:24

Bohemian · Accepted Answer · 2014-06-04T22:24:00.957

The key to solving this problem is putting the condition in payment into the join condition:

SELECT 
    c.customer_id,
    c.customer_companyname,
    c.customer_status,
    SUM(p.payment_amount) as total_received,
    b.branch_id,
    b.branch,
    m.manager_id,
    m.manager_name
FROM customers c
LEFT JOIN payments p on p.customer_id = c.customer_id
  AND p.payment_status = 1 -- Payment condition here!
LEFT JOIN branches b ON b.customer_id = c.customer_id
LEFT JOIN managers m ON m.branch_id = b.branch_id
WHERE c.customer_status = 1
GROUP BY
    c.customer_id,
    c.customer_companyname,
    c.customer_status,
    b.branch_id,
    b.branch,
    m.manager_id,
    m.manager_name

Two main points:

The reason you can't have the payments condition in the WHERE clause is that missed joins have nulls in the joined column, so the test on payment status won't be true and you'll filter out customers with no payments with status 1, instead if returning 0 for the sum. By putting the condition into the join condition, it only matches suitable rows, but will still allow the customer row to be returned without any.
It's OK to have non-key related conditions on a join condition. Most people don't realise this. It's good to keep in mind.

Note that your join conditions look incorrect. I would expect that branch should match on c.branch_id = b.brach_id and similarly for manager, but I'll leave that to you to sort out.

Amazing. 3 JOINS. Man was I off. I appreciate it a million. Worked almost perfectly just had to add `GROUP BY c.customer_id` - Thanks again — Onimusha, Jun 03 '14 at 22:39
No. Don't group by customer_id - it won't work properly. See my edit for the correct group by and some notes - especially read the last sentence. Good luck :) — Bohemian, Jun 03 '14 at 22:42
Excellent. Yes the branch_id should ideally be matched with customer but in this specific case it was required for a few other reasons (related to other tables and features). PS. Error when using `GROUP BY 1,2,3,5,6,7,8` error: `Can't group on 'total_received'` — Onimusha, Jun 04 '14 at 09:10
Then the columns you are selecting do not align with those in my query. You must either list the number (counting from 1) of every selected column that is *not* an aggregated value, or you can name them. I prefer using numbers because it's easier to type, but to settle this I have change the query to the named version - see edit — Bohemian, Jun 04 '14 at 22:23

score 1 · Answer 2 · answered Jun 03 '14 at 22:43

select c.customer_id,c.customer_companyname,c.customer_status,SUM(p.payment_amount) as total_received from customers c
 left join payments p on (p.customer_id=c.customer id and p.payment_status=1)
 left join branches b on (b.customer_id=c.customer_id) 
 left join managers m on (m.branch_id=b.branch_id) 
 where customer_status=1 group by p.customer_id order by p.customer_id

It's a complex query and I don't have your database to compare against, but I think the solution for you is to left join all your tables to customers and then group by the table you are summing.

Here's an example of aggregating across tables, in case there's a hole in my code: http://www.artfulsoftware.com/infotree/qrytip.php?id=105

MySQL left join with multiple tables and where clauses

2 Answers2