Solved

A query

Posted on 2013-12-07
3
236 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
t-sql money data type decimal places 4 25
Sort by Month and Year - SQL 3 22
Sql query 34 17
SQL Split character from numbers 3 16
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now