Solved

A query

Posted on 2013-12-07
3
242 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 48

Assisted Solution

by:Dale Fye
Dale Fye 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 27

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 27

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

622 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