I'm trying to make a query that returns the following: All users such that: -They are not an admin or owner account -They have the same client_id as the project's client_id -They are not already in the project_users table with entry project_users.project_id = 9
Here is my MySQL query:
SELECT `users`.`id` as id, `users`.`first_name` as first_name, `users`.`last_name` as last_name, `users`.`username` as username
FROM (`users`)
JOIN `projects` ON `projects`.`client_id` = `users`.`client_id` AND projects.id = 9
LEFT OUTER JOIN `project_users` ON `users`.`id` = `project_users`.`user_id`
WHERE `users`.`user_type` != 'Admin'
AND `users`.`user_type` != 'Owner'
For some reason, this query seems to return all non-super(not owner or admin) users with the same client_id as the project, but does NOT exclude those already in the project_users table (ie. the LEFT OUTER JOIN statement isn't working).
Can anyone tell me what is wrong with the query?
Thanks!