CODEWITHSUNDEEP
sqlmysqldatabase
philly77
philly77

Reputation: 519

SQL query to extract projects that user has access to

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

Upvotes: 1

Views: 577

Answers (3)

Roee Adler
Roee Adler

Reputation: 33990

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.

Upvotes: 1

David Andres
David Andres

Reputation: 31781

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

Upvotes: 1

seth
seth

Reputation: 37277

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

Upvotes: 1

Related Questions