SQL, retrieving only entries that all the IDs of its many-to-many relation are in an array

user_7148436 0
Viewed 30

need some help with a SQL query. Below I wrote an example of what I want:

I need to retrieve entries that the user has privileges to see all of its components.

The privileges are stored in an array, for example, I'm a user with the following privileges: [1,2,3]

In the database, I have a table with the following entries:

entry_id entry_name other_info
1 "Example A" ...
2 "Example B" ...
3 "Example C" ...

And I have a many-to-many relationship with the components, meaning which components each entry uses:

entry_id component_id
1 1
1 2
1 3
1 4
2 1
2 3
3 2

I would like to retrieve only entries 2 and 3 for my user, because it's the ones I have access to all of its components. Entry 1 would not be retrieved, since I don't have component 4 in my privileges array.

Example of the desired output:

entry_id entry_name
2 "Example B"
3 "Example C"
hana sql
1 Answers

First, establish a query of all the entry_id's that have records where your user does not have permission. Then use that in your where clause on your query.

--here is a list of entry_id's where components exist that your user 
--does not have permission to
SELECT DISTINCT entry_id FROM tbl2 WHERE component_id NOT IN (1,2,3)


SELECT entry_id, entry_name
FROM tbl1
WHERE entry_id NOT IN (  SELECT DISTINCT entry_id FROM tbl2 WHERE 
component_id NOT IN (1,2,3)   )

If component_id can be NULL you'll want to check for that.

user_49036160
Related