SP Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SP Help

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2009-09-21 : 16:33:55

I have a db that contains file information for files that sit on the server. In the db there are tables that include: FileInfo, Distribution, Users DistributionList ans AsocDist

The Distribtion table contains a name for a distribtion, the distribtionList the users associated witht the distribtion, and the AssocDist table contains the FileInfo and Dist association

Table: FileInfo
Fields: FileID, FileName, Location
Data: 1, SomeTxt.txt, ServerA
2, Spreadsheet.xls, ServerB

Table: Distribtion
Fields: DistID, DistName
Data: 1, Month-End Recievables
2, Year-End Profits

Table: DistribtionList
Fields: DistID, UserID
Data: 1, 12
1, 15

Table: AssocDist
Fields: FileID, DistID
Data: 1, 1
2, 2

Table: Users
Fields: UserID, Fname, LName
Data: 12, Mickey, Mouse
15, Daffy, Duck

Query: Get FileInfo data where userID = 12

Results Wanted: 1, SomeTxt.txt, ServerA

I need to get the FileName, Location for a specific user and I can't figure out how to get it

Any help would be appreciated.

Thanks

ann
Posting Yak Master

220 Posts

Posted - 2009-09-21 : 16:46:42

So I came up with:

Select * From UploadedFiles
where UploadFileID in
(
select UploadFileID from AssocDist
Where DistID in
(select DistID from DistList Where userID = 12)
)

Does that seem right? It gives me the results I want, but I'm working with such a small subset of data for testing - or is there a better way to do thi?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-21 : 21:31:23

[code]
DECLARE @FileInfo TABLE
(
FileID int,
[Filename] varchar(20),
Location varchar(10)
)

INSERT INTO @FileInfo
SELECT 1, 'SomeTxt.txt', 'ServerA' UNION ALL
SELECT 2, 'Spreadsheet.xls', 'ServerB'

DECLARE @Distribution TABLE
(
DistID int,
DistName varchar(30)
)

INSERT INTO @Distribution
SELECT 1, 'MONTH-END Recievables' UNION ALL
SELECT 2, 'YEAR-END Profits'

DECLARE @DistributionList TABLE
(
DistID int,
UserID int
)

INSERT INTO @DistributionList
SELECT 1, 12 UNION ALL
SELECT 1, 15

DECLARE @AssocDist TABLE
(
FileID int,
DistID int
)

INSERT INTO @AssocDist
SELECT 1, 1 UNION ALL
SELECT 2, 2

DECLARE @Users TABLE
(
UserID int,
Fname varchar(10),
LName varchar(10)
)

INSERT INTO @Users
SELECT 12, 'Mickey', 'Mouse' UNION ALL
SELECT 15, 'Daffy', 'Duck'

SELECT fi.FileID, fi.[Filename], fi.Location
FROM @DistributionList dl
INNER JOIN @AssocDist ad ON dl.DistID = ad.DistID
INNER JOIN @FileInfo fi ON ad.FileID = fi.FileID
WHERE dl.UserID = 12

/*
FileID Filename Location
----------- -------------------- ----------
1 SomeTxt.txt ServerA

(1 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources