Checking whether a foreign relationship exists

Question

I have a couple of tables within an SQLite v3.37 meant to track Users, Roles, and Permissions.

For the sake of the question, the users has just an id and an email. roles and permissions are also simple, with just an id and a roleName and permName respectively.

CREATE TABLE permissions (id integer primary key not null, permName text unique not null, dateCreated text default CURRENT_TIMESTAMP, dateUpdated text default CURRENT_TIMESTAMP);

I have two auxiliary tables, userRoles and rolePerms which track what roles a user has been assigned, and what permissions have been assigned to each role respectively.

CREATE TABLE userRoles (userID integer not null, roleID integer not null, FOREIGN KEY (userID) REFERENCES users(rowid), FOREIGN KEY (roleID) REFERENCES roles(id));

Here is a rough diagram of the schema:

I am trying to create an SQL query that from an email address and a permName, determine if that user has been granted a role with that permission.

I have tried writing a query something like this:

SELECT users.rowid, users.email, roles.rowid, roles.roleName, permissions.rowid, permissions.permName, userRoles.*, rolePerms.* FROM users
    INNER JOIN userRoles on userRoles.userID=users.rowid AND userRoles.roleID=roles.roleID 
    INNER JOIN rolePerms on rolePerms.roleID=roles.rowid 
    WHERE users.email = '[email protected]' AND permName='testPermission';

But this query obviously isn't getting the job done. I have seen this question about joining 4 tables, but those tables are all related to a single table, not two like the schema I am using.

EDIT: I believe the following query to be working:

SELECT DISTINCT(permName) FROM users INNER JOIN userRoles ON userRoles.userID=users.rowID INNER JOIN roles ON userRoles.roleID=roles.id INNER JOIN rolePerms ON rolePerms.roleID=roles.id INNER JOIN permissions ON permissions.id=rolePerms.permID WHERE users.email='[email protected]';

The big change is to reference roles via a JOIN ON userRoles, otherwise the SQL query is not aware of the table(?)

Answer 1

Your query is almost correct, you just need to join with the permissions table as well, plus remove some confusion about column names and remove an extra join condition:

SELECT users.rowid, users.email, roles.id, roles.roleName, permissions.id, permissions.permName, userRoles.*, rolePerms.* 
FROM users
INNER JOIN userRoles ON userRoles.userID=users.rowid
INNER JOIN roles ON roles.id=userRoles.roleId
INNER JOIN rolePerms ON rolePerms.roleID=roles.id
INNER JOIN permissions ON permissions.id=rolePerms.permID
WHERE users.email= '[email protected]' AND permissions.permName='testPermission';