Mysql – Multiple left join where not in another table

MySQLselect

I have the following tables (there is more detailed data in each one, I just put the minimum to create a sample):

__________  _____________  _____________  _____________
| person |  | provider  |  | client    |  | user      |
| id     |  | id        |  | id        |  | id        |
| name   |  | person_id |  | person_id |  | person_id |
----------  | name      |  | name      |  | name      |
            -------------  -------------  -------------

I'm trying to create a list where I got all the providers together with all clients but not the users (only the ones that are providers and/or clients).

I started with this query:

SELECT person.id, person.name,
IF(client.id IS NOT NULL, 'Cliente', '') as cliente, 
IF(provider.id IS NOT NULL, 'Fornecedor', '') as fornecedor
FROM person
LEFT JOIN provider ON person.id = provider.person_id
LEFT JOIN client ON person.id = client.person_id

But with this I also list users that are not providers nor clients, so I add this part to the code:

JOIN user ON person.id = user.person_id 
WHERE user.id IS NULL

But now I lost the people that are User + provider/client.


BASIC DATA (Mock data)

person {
    id: 1,
    name: "test 01";
    id: 2,
    name: "test 02";
    id: 3,
    name: "test 03";
    id: 4,
    name: "test 04";
}
client {
    id: 1,
    person_id: 3
    name: "cli 01";
}
provider {
    id: 1,
    person_id: 3
    name: "prov 01";
    id: 2,
    person_id: 4
    name: "prov 02";
}
user {
    id: 1,
    person_id: 1
    name: "user 01";
    id: 2,
    person_id: 3
    name: "user 02";
}

The Data that I want is:

person_id: 3 -- it's a client
person_id: 4 -- it's a client and a provider

How could I list all providers + clients and not the users (but show the users that have provider/client)

Best Answer

According to comment you are not interested in users at all, you just want the person to be client or provider (boolean logic, so it can be both):

WHERE (client.id IS NOT NULL OR provider.id IS NOT NULL)