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.