MySQL security group view permission query I have a table of records that are only viewable by users with the correct group permission. The records may be viewable by multiple groups. Users may also be in multiple groups.

record

+----+-------------------+
| id |    record_data    |
+----+-------------------+
|  1 | Some sample data  |
|  2 | More data         |
|  3 | Some more data    |
+----+-------------------+


groups

+----+-------------+
| id | description |
+----+-------------+
|  1 | Group A     |
|  2 | Group B     |
|  3 | Group C     |
|  4 | Group D     |
+----+-------------+


user

+----+------+
| id | name |
+----+------+
|  1 | Levi |
|  2 | Bill |
|  3 | Sam  |
|  4 | Jen  |
|  5 | Amy  |
+----+------+


I have created lookup tables to match the record to the groups that have permission to view them. I have also created a lookup table that matched the users with the groups they are members of.

record_group

+----+-----------+----------+
| id | record_id | group_id |
+----+-----------+----------+
|  1 |         1 |        2 |
|  2 |         1 |        4 |
|  3 |         2 |        1 |
|  4 |         3 |        2 |
|  5 |         3 |        3 |
|  6 |         3 |        4 |
+----+-----------+----------+


So in the table above, record one is viewable by users in group 2 and 4. Record 2 is viewable by users in group 1, and record 3 is viewable by users in group 2,3 and 4.

user_group

+----+---------+----------+
| id | user_id | group_id |
+----+---------+----------+
|  1 |       1 |        1 |
|  2 |       1 |        2 |
|  3 |       1 |        4 |
|  4 |       2 |        1 |
|  5 |       2 |        4 |
+----+---------+----------+


Above, User 1 is in group 1,2 and 4. User 2 is in group 1 and 4.

I am trying to create a query that will show all the records that a particular user has the correct group permissions to view. Example. I want to list all the records that user ID 2 can view and ignore the others.

The query for user 2 should return record 2,3 and 6

I need to do this in a single query as it will be plugged into an existing framework.

My understanding is that I can do this with the MySQL IN function but I cant seem to get it to work with two sets, example (Select id from member_groups) IN ()

So my Question, how do I build a MySQL query that selects all the records user one has permissions to view?

I'm sorry I cant provide more information or example query, I'm not sure how to start.

SQL Fiddle http://sqlfiddle.com/#!9/e40ee6/1
SELECT DISTINCT record.id AS id, details from record 
LEFT JOIN record_group ON record.id = record_group.record_id
WHERE record_group.group_id IN (SELECT group_id from user_group WHERE user_id = 2)


http://sqlfiddle.com/#!9/e40ee6/12