SQL query to extract projects that user has access to

Question

I have a project table with projectId as primary key:

**projectId**, projectName, dateCreated, etc.

Then, I have a userAccess table using a composite primary key:

**userId**, **projectId**

Only the users listed in the userAccess table for each project will be able to view the projects.

Now, I am trying to write a MySQL query which will allow me to get a list of projects from the project table to which the user with id USER_ID has access to.. I have been trying this for a few hours now, and although I am guessing I have to do a join here, I am not able to get my mind to wrap around this. Any help greatly appreciated :)

Answer 1

SELECT project.projectId, project.projectName
FROM project
INNER JOIN userAccess
ON project.projectId=userAccess.projectId
WHERE userAccess.userId = ?

Answer 2

Not familiar with MySQL's brand of SQL, but either of the following should work:

SELECT *
FROM userAccess u
INNER JOIN project p on p.projectId = u.projectId
WHERE u.userId = USER_ID

or...

SELECT *
FROM userAccess u, project p
WHERE p.projectId = u.projectId 
AND u.userId = USER_ID

Answer 3

Just to give the subquery alternative:

SELECT projectId, projectName
FROM project
WHERE projectId IN
    (
        SELECT projectId
        FROM userAccess
        WHERE userId = YOUR_USER_ID
    )

This is probably less efficient than the JOIN option, but very readable.