sqldatabasefilteringcommon-table-expressionhierarchy

Hierarchy filtering using SQL


I have 3 example tables in my database as below: (1) Org Chart (2) User (3) Invoice

I would like to use SQL to write a view that has dynamic filtering features based on hierarchy. For example, If I am the user "Jack", who is the manager of IT department, when I execute the view, I should only see the invoice ID of 1 and 3, since invoice ID 1 belongs to me and invoice ID 3 belongs another employee "Nicole" under my department IT. Also, I should not be able to see invoice ID 2 and 4 since these invoices do not belong to users under department IT.

Is this possible to achieve using SQL in any of relational database? If so, please also provide SQL statements that can achieve it. Thank you.

enter image description here

enter image description here

enter image description here


Solution

  • I hope this helps you:

    WITH 
    a AS 
    (SELECT team FROM user
    WHERE user_id=1),
    
    b AS (
    SELECT DISTINCT department as team, department as sub_team
    FROM org
    UNION
    SELECT DISTINCT department as team, section as sub_team
    FROM org
    UNION
    SELECT DISTINCT department as team, unit as sub_team
    FROM org
    UNION
    SELECT DISTINCT section as team, section as sub_team
    FROM org
    UNION
    SELECT DISTINCT section as team, unit as sub_team
    FROM org
    UNION
    SELECT DISTINCT unit as team, unit as sub_team
    FROM org),
    
    c AS (
    SELECT b.sub_team FROM a, b
    WHERE a.team=b.team
    ),
    
    d AS (
    SELECT user_id FROM user, c
    WHERE user.team=c.sub_team
    )
    
    SELECT invoice_id FROM invoice, d
    WHERE invoice.user_id = d.user_id;
    

    There may be a more elegant solution, but this one works.