Skip to content
Advertisement

SQL- Get results based on four dependable tables with relations

I am trying to write sql query for a problem I am facing, but I can not get my head around it.

  • I have team_member table with team_id and member_id.

  • role table that stores roles with team_id (..name, slug)

  • team_member_role table that is a many-to-many relationship between team_member and role (contains team_member_id and role_id).

  • role_ability table, which stores role_id. (..action, subject)

In my code I get only get to work with member_id to extract results.

Use case: I get the member_id = 1 and need to retrieve all roles with it’s abilities of that specific member.

Team member

id team_id member_id
92 3 1

Role

id team_id name slug
1 3 ROLE_MEMBER role_member
2 2 ROLE_ADMIN role_admin

Team Member role

role_id team_member_id
1 92
2 92

Role Ability

id role_id action subject
1 1 read workspace_members
2 1 create workspace_members
3 1 edit workspace_members

Can someone please help with the issue. Thanks!

SELECT r.*
FROM role r
JOIN team_member_roles tmr ON r.id = team_member_roles.role_id
JOIN role_ability ra ON tmr.role_id = ra.id

I am pretty new in writing complex sql queries and I have basic mock up of how it should look and maybe I could get to the result with team_member_id and not with member_id which I need to use.

The overall result would be like:

member_id name abilities
1 ROLE_MEMBER read
create
edit

Advertisement

Answer

(Your data model looks to be really broken – which is probably making writing this query appear harder than it is – but we’ll ignore that for the sake of answering this question).

Try this:

SELECT
TM.MEMBER_ID
,R.NAME
,RA.ACTION
FROM TEAM_MEMBER TM
INNER JOIN ROLE R ON TM.TEAM_ID = R.TEAM_ID
INNER JOIN ROLE_ACTION RA ON R.ID = RA.ROLE_ID
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement