Getting Correct User Group

Databases
1

Hi all,

I have a system in which users can create their own user_groups for their own project. A user can belong to many projects but can have only one group per project. My tables:

users (userid, username, active_projectid)
projects(projectid, name)

project_groups(groupid, projectid, name)
project_users(pid, userid, groupid)

I’m struggling to create a query that fetches the user’s group based on their active_projectid.


SELECT users.userid, users.username FROM users
LEFT OUTER JOIN project_groups ON project_groups.projectid = users.active_projectid 
LEFT OUTER JOIN project_users ON project_users.userid = users.userid
WHERE users.userid = 5

The above obviously doesn’t work because project_groups could contain multiple groups for a single project (e.g. Admin, Programmer, Writer). I thought of doing something like adding another value to the users table like active_groupid and change that whenever the user changes project. But I was just wondering if there was some way to be able to select the user’s group without having to do this.

Notes: A user may not be in any group or belong to any project (such as they just registered on the system and haven’t created a project yet). This is why it needs to be LEFT OUTER JOIN and also why I can’t select from project_users instead.

Any help would be appreciated!

2

if a project can have many groups then a user’s active project can have many groups, so asking for the user’s (single) group doesn’t make sense

3

Sorry, I don’t think I explained myself.

A project can have multiple users assigned to it (project_users). Sometimes, the admin of the project may wish to create their own user groups in order to give power to the users in their project. (project_groups). So in order to get the user’s power for a particular project, I need to grab their project_group based on their active_projectid.

Does that make a little more sense?

project_group also contains columns such as files, tasks, tickets. So when you get the correct group, you know what power they have (if they have access to the file system, tasks system or ticket system).

4

that’s actually quite simple

SELECT users.username 
     , project_groups.groupid
     , project_groups.name
  FROM users
LEFT OUTER 
  JOIN project_groups 
    ON project_groups.projectid = users.active_projectid 
 WHERE users.userid = 5
5

Aha yeah, but a project can have multiple project_groups (but a user can only belong to one of them). So the above query will only get the first group within project_groups rather than the one the user actually belongs to. E.g.

users (userid, username)
1, Bob
2, Alph

projects (projectid, name)
1 Test Project
2 New Project

project_groups (groupid, projectid, name)
1, 1, Admin
2, 1, Programmer

project_users (userid, groupid)
1, 1
2, 1

In this case, Bob and Alph are both admins of the first project (test project).

6

you’ve completely lost me

unless you can provide some more robust and comprehensive sample data, i have no idea what you’re after, sorry

7

Haha okay no problem. I know the solution to it but I kind of wanted to do it without adding extra rows to my tables. Don’t worry about it, thank you for attempting to help!

8