Solved

A query

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Trouble with <> 2 21
Get Next number from Stored Procedure 8 23
What is this datetime? 1 20
Stored Procedure needs owner to execute 5 20
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

828 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