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” |
Advertisement
Answer
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.