Multiple left join where not in another table
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)