Using MS SQL Server 2008.
I need help with a query that i think can be solved easily by an expert.
I have six tables that I need to join in a view. There are three base tables. These are Users, Pens and Applications.
The Users table contains users and has a unique key called usr_id (integer)
The Pens table contains pens and has a unique key called pen_id(integer).
The Applications table contains a list of applications and has a unique key called app_id(integer).
Then there are two tables called UsersPens and UsersApplications. These simply list which pens and applications a user has access to.
UserPens has two columns which are usr_id and pen_id.
UserApplications has two columns which are usr_id and app_id.
And finally we have the sixth table that list documents. These documents have been generated by applications and completed using pens.
A user is only allowed to view documents when they have both the pen and the application in the UsersPens and UsersApplications table. i.e. their usr_id is matched with the pen_id and app_id.
The Documents table has pen_id and app_id columns. It also has a unique column called doc_id (integer).
I need a sql statement, that given a usr_id, will return a list of the documents (doc_id column) the user can view.