Problem

Given a table of users and a table of permissions, write a SQL query to find all users who have the permission to view a certain resource.
Example input:
users:
id | name
1 | John
2 | Jane
3 | Bob
permissions:
user_id | resource
1 | resource1
1 | resource2
2 | resource2
3 | resource1
Example output:
id | name
1 | John
2 | Jane

Solution

SELECT users.id, users.name
FROM users
JOIN permissions ON users.id = permissions.user_id
WHERE permissions.resource = 'resource2';

/*

The solution above is optimal because it uses a JOIN to combine the two tables and then filters the results to only include the users who have the permission to view the resource.

*/