combing mysql outer joins in a search

Question

I have 3 tables in my database

PROJECTS
- id
- name
- date_created
- user_created
- status (or for disabled, 1 for enabled)

PROJECTS_MANAGERS (a project may have 0 or more managers)
- id
- project_id
- manager_username

PROJECTS_MEMBERS (a project may have 0 or more members)
- id
- project_id
- member_username

I'm trying to create a mysql join query to list all the projects that a particular user is involved in where the status is equal to 1. Specifically, a query that will list ALL projects where username "bob" is in the "use_created", "manager_username", or "member_username" fields and where the projects.status field is 1. But the trick is there could be multiple managers or members with the same project_id (differsnt ids and member_usernames of course).

I've tried this code, but it doesn't seem to work (gives me the error '1054 unknown column 'projects.status' in where clause). I tried doing a "full join" but I've come to learn mysql does not support this:

SELECT *
FROM
    `projects` as theProjects

LEFT OUTER JOIN
    projects_managers
            AS theManagers
                    ON theProjects.id = theManagers.project_id
WHERE
    projects.status = 1
UNION ALL

SELECT *
FROM
    `projects` as theProjects
RIGHT OUTER JOIN
    projects_members
            AS theMembers
                    ON theProjects.id = theMembers.project_id
        WHERE
    projects.status = 1

Any help would be greatly appreciated.

Answer 1

How about:

SELECT projects.*
FROM projects
LEFT JOIN projects_managers managers ON managers.project_id=projects.id
LEFT JOIN projects_members members ON members.project_id=projects.id
WHERE status=1 AND
      (user_created="bob" OR managers.username="bob" OR members.username="bob")
GROUP BY projects.id