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):