Results 1 to 2 of 2
Thread: Please help me with this query.
Nov 26, 2004, 04:04 #1
- Join Date
- Mar 2002
- Miami, Florida - Caracas, Venezuela
- 0 Post(s)
- 0 Thread(s)
Please help me with this query.
I'm building a simple system that allows someone in my client's onganization to post files for certain users to download. In our case the best way for the administrator of the system to manage access to files is to have users, groups, documents and overrides. The point being that you create groups, add documents to groups, then add users to groups, then add or remove documents on a user by user basis.
So here are my tables:
I need to create a query that returns all documents a user has access to based on what group the user belongs to and taking into account that the document does NOT appear related to the user in documents_users with switch set to 0. Now, after getting that, add the files that DO appear related to the user in documents_users with switch set to 1.
Basically get documents for user based on group and add or remove documents to the users results based on documents_users.switch.
Please, please, please help!!! I'm loosing too much sleep over this!!!
Nov 26, 2004, 07:55 #2
- Join Date
- Jul 2002
- Toronto, Canada
- 63 Post(s)
- 3 Thread(s)
select d.id , d.name , d.data from users as u inner join groups_users as gu on u.id = gu.user_id inner join documents_groups as dg on gu.group_id = dg.group_id inner join documents as d on dg.document_id = d.id left outer join documents_users as du on u.id = du.user_id and du.switch = 0 where u.id = 937 and du.user_id is null union all select d.id , d.name , d.data from users as u inner join documents_users as du on u.id = du.user_id and du.switch = 1 inner join documents as d on du.document_id = d.id where u.id = 937