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

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