Solved

A query

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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Date 6 42
TDE for SQL Web Edition 1 42
SQL Query returning inflated numbers 1 14
Importing from CSV to SSMS 2 17
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

710 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