Solved

A query

Posted on 2013-12-07
3
239 Views
Last Modified: 2013-12-11
Hello,

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.
0
Comment
Question by:soozh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 50 total points
ID: 39703090
Select Users.Usr_ID, Documents.doc_ID
FROM (
SELECT Users.Usr_Id, Pens.Pen_ID, UserApplications.App_ID
INNER JOIN UserPens on Users.Usr_ID = Pens.Usr_ID
INNER Join UserApplications on Users.Usr_ID = UserApplications.Usr_ID
WHERE Users.User_ID = [some value]
) as UserPenApps
INNER join Documents
ON UserPenApps.Pen_ID = Documents.Pen_ID
AND UserPenApps.App_ID = Documents.App_ID
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 400 total points
ID: 39703223
Try this:
-- create view
create view UsersDocuments
as
select 
	u.usr_id,
	d.doc_id 
from
	Users u
	inner join UsersPens up
		on up.usr_id=u.usr_id
	inner join UserApplications ua
		on ua.usr_id=u.usr_id
	inner join Documents d
		on d.pen_id=up.pen_id
		and d.app_id=ua.app_id
go

-- use the view
select 
	* 
from 
	UsersDocuments
where 
	usr_id=<usr_id_value_here>

Open in new window

Mahe sure you have the following ixdexes:

On Users, UsersPens  and UserApplications tables on usr_id column

On Documents table a composite index on pen_id + app_id columns
0
 
LVL 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 50 total points
ID: 39704137
To give you just what you asked for, a query to return the list of Documents try this
SELECT  D.doc_id
FROM    Users U
INNER JOIN UsersPens UP ON UP.usr_id = U.usr_id
INNER JOIN UserApplications UA ON UA.usr_id = U.usr_id
INNER JOIN Documents D ON D.pen_id = UP.pen_id
                          AND D.app_id = UA.app_id
WHERE   U.usr_id = Your_Selected_User_Id

Open in new window

0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
find SQL job run average duration 24 55
Change this SQL to get all nodes 3 38
Using datetime as triggers 2 20
What Is an Error? 2 24
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question