How to join 4 tables to get data from two tables-CodePudding

I am trying to select all pages that this user has access to whether it is direct access or group access.

I tried this but it did not return the results as expected.

SELECT DISTINCT  
    pages.id AS page_id, 
    pages.name AS page_name, 
    pages.description AS page_description,
    pages.type AS page_type, 
    group_page.group_id AS page_group_id, 
    user_page.user_id AS page_user_id 
FROM 
    pages 
JOIN
    user_page ON pages.id = user_page.page_id 
JOIN 
    group_page ON pages.id = group_page.page_id 
JOIN 
    user_group ON group_page.group_id = user_group_.group_id 
WHERE 
    user_page.user_id =  23

I have provided sample data with my schema below:

Page table:

page_id  | page_name | page_description
--------- ----------- ------------------
4        |  name3    | descr 
6        |  name2    | description
5        |  name8    | description

user_page table:

    user_id | page_id
------------ ----------
    23      |   4
    12      |   6
    12      |   6

group_user table:

 user_id   | group_id
----------- -----------
    4      |   14
    4      |   16
    23     |   16

group_page table:

 page_id   | group_id
----------- -----------
    4      |   14
    6      |   16

Check out here https://www.db-fiddle.com/f/6MnsujPF8foREq2Cwt8EDn/2

I am trying to retrieve page 4 for user 7

CodePudding user response：

I would go for something like this, go easy on those DISTINCT clauses, they are generally bad practice. This worked nicely in a test on the linked page

select 
p.*
from pages p
inner join group_page gp on gp.page_id=p.page_id
inner join group_user gu on gu.group_id=gp.group_id
where gu.user_id = '7'
union
select 
    p.*
from 
    pages p
inner join
    user_page up ON p.page_id = up.page_id
where up.user_id = '7'

CodePudding user response：

Just a note, your fiddle doesn't have a page_id 4, so no results were returning. I updated page_id 3 to page_id 4.

I join group_user and group_page so that I can select the user_id and page_id (group access), and then UNION that to the user_id and page_id from the user_page table (direct access). UNION should return distinct records.

Then I join this result to the pages table with WHERE user_id = 7.

https://www.db-fiddle.com/f/3vqKF2amyFcPvzaFP8hD68/1

select p.*
from pages p
join (

  select up.user_id, up.page_id
  from user_page up
  
  UNION
  
  select gu.user_id, gp.page_id
  from group_user gu
  join group_page gp on (gu.group_id = gp.group_id)
  
) ids on (p.page_id = ids.page_id)
where ids.user_id = 7