Closed. This question needs details or clarity . It is not currently accepting answers.

Want to improve this question? Add details and clarify the problem by editing this post .

Closed 3 days ago.
This post was edited and submitted for review 3 days ago.
Improve this question

This is in Microsoft Azure. I have a list of reports that have specific organizations associated with it. For example

CREATE TABLE Reports (
reportId INT,
OrganizationID INT
);
INSERT INTO Reports (reportId, OrganizationID) VALUES
(1, 1),
(1, 2),
(2, 1),
(3, 1),
(3, 2),
(3, 3);

I also have a user table that's associated to organizations. For example:

CREATE TABLE Users (
User CHAR(1),
OrganizationId INT
);
INSERT INTO Users (User, OrganizationId) VALUES
('a', 1),
('a', 2),
('b', 2),
('c', 1),
('c', 2),
('c', 4);

What I need to do is figure out who has access to which report based on their organizations - the user has to have all the organizations that are listed in the report in order to have access to that report. So with the above data I would expect to see user a has access to both report 1 and report 2, user b doesn't get access to any reports and user c gets access to report 1 and report 2.

Output example:
| User | ReportId |
| ------------ | ------------ |
| a | 1 |
| a | 2 |
| c | 1 |
| c | 2 |

I know I can use a SQL to loop through the reports one by one to figure out who has access to which report, is there a way to do that without looping through the reports and just in straight SQL? Would appreciate any help! Thanks in advance.