CODEWITHSUNDEEP
mysqlsqljoinleft-join
user1418227
user1418227

Reputation: 201

Tricky MySQL query with joins?

I have 4 tables (among a lot of others - just showing the relevant ones). They are:

I'm trying to build a query that will return to me a list of all users, their role name, all chapters per user, and whether or not they have view access to each chapter. So lets say there are 2 users and 2 chapter. User 1 is role_id=1 and User 2 is role_id=2. And the chapter_role_view_permissions has 1 record ( id:1 | chapter_id: 1 | role_id: 1 ) which means only users with role_id=1 can view chapter 1.

The query result I am looking for should be something like:

|---users.id---|--roles.id---|---chapters.id--|-can_view_chapter-|

|------ 1 ------|----- 1 -------|------- 1 --------|----------- 1 -----------|

|------ 1 ------|----- 1 -------|------- 2 --------|----------- 0 -----------|

|------ 2 ------|----- 2 -------|------- 1 --------|----------- 0 -----------|

|------ 2 ------|----- 2 -------|------- 2 --------|----------- 0 -----------|

Any idea what the query might look like? I don't want to have to make a new field in chapter_role_view_permissions that says "allowed" and insert new records for each role type with either allowed=1 or allowed=0

Upvotes: 0

Views: 814

Answers (3)

nawfal
nawfal

Reputation: 73183

SELECT    u.id, r.id, c.id, IFNULL(c.id, 0) AS can_view_chapter
FROM      users AS u
JOIN      roles AS r ON r.id = u.role_id
LEFT JOIN chapter_role_view_permissions AS p ON p.role_id = r.id
JOIN      chapters As c ON c.id = p.chapter_id

Upvotes: 0

Marlin Pierce
Marlin Pierce

Reputation: 10089

SELECT users.name, roles.name, chapters.name
FROM users
  LEFT OUTER JOIN roles ON users.role_id = roles.id
  LEFT OUTER JOIN chapter_role_view_permissions link ON link.role_id = roles.id
  LEFT OUTER JOIN chapters ON chapters.id = link.chapter_id

Upvotes: 2

Kenedy
Kenedy

Reputation: 229

As you said:

I'm trying to build a query that will return to me a list of all users, their role name, all chapters per user, and whether or not they have view access to each chapter

Just translate your own words in a query, in the same order:

SELECT users.id, roles.name, chapters.name FROM users
INNER JOIN roles ON  users.role_id=roles.id
INNER JOIN chapters ON users.id=chapters.id
WHERE users.id=USER_ID

Anyway, you have to explain better: how are you relating chapters and users? I assumed chapters.id is the same as user.id

Upvotes: 0

Related Questions