Question 281602 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres Check if One to many relationship has all values in another relationship

I have a table which contains all the Reports

   report_id   | Name 
---------------+-------------
      1        | Sales Q3
      2        | Sales Q2

I have a relation table which contains one to many relations about the report with dept

  report_id   |  dept_id  
-----------------+---------------
      1       | 4
      1       | 5
      2       | 4

Relation Table signifies report(1) contains data about dept(4 & 5)
and report(2) contains data about dept(4)

I have another table which specifies relation between users and departments.

  user_id     |  dept_id  
-----------------+---------------
      1       | 4
      1       | 5
      2       | 4
      3       | 5

Users table also signifies the relation between user and dept.

I need to fetch all set of reports for a particular user only if the user has relation to all the department in that particular report.

For example

User(1) has access to dept(4,5) so query should return both report(1,2)

User(2) has access to dept(4) so query should return only report(2) and should not return report(1) as it has relation with dept(5)

User(3) has access to dept(5) so query should not return any reports as both the report has relation with dept(4)

A query like

Select * from reports inner join relation on relation.report_id = reports.report_id ( some condition to satisfy all rows of relations.dept_id are present in users.dept_id table ) where users.user_id = 1

Given the above use case, Are these tables right way to maintain the relationship between reports,department and users?

Tags:

2

Answers


  1. 0

    We can try a join approach with the help of COUNT() used as a window function:

    WITH cte AS (
    SELECT rels.user_id, rpt.report_id, rpt.Name,
           COUNT(rel.dept_id) OVER (PARTITION BY rels.user_id) rpt_cnt,
           COUNT(*) OVER (PARTITION BY rels.user_id) total_cnt
    FROM relations rels
    LEFT JOIN relation rel ON rel.dept_id = rels.dept_id
    INNER JOIN reports rpt ON rpt.report_id = rel.report_id
)

SELECT user_id, report_id, Name
FROM cte
WHERE rpt_cnt = total_cnt
ORDER BY user_id, report_id;

    The idea in the above is that we try to find for each user’s department, a matching report. We return all of a given user’s reports in the case where all departments match to a report.

    Login or Signup to reply.
  2. 0

    "Find reports for any department the given user is part of, and not released for any department the user is not."

    WITH u_dep AS (
   SELECT debt_id
   FROM   users
   WHERE  user_id = 1
   )
SELECT report_id
FROM  (
   SELECT DISTINCT report_id
   FROM   relation
   WHERE  dept_id IN (TABLE u_dep)
   ) u_rep
WHERE  NOT EXISTS (
   SELECT FROM relation rl
   WHERE  rl.report_id = u_rep.report_id
   AND    rl.dept_id NOT IN (TABLE u_dep)
   );

    Based on a couple of assumptions, the question did not disclose. Like: all involved columns are defined NOT NULL.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search