June 2, 2010 at 5:11 pm
Hello all!
I've been racking my brain for the past couple of days trying to figure this out. I'm not a SQL guru, but I like to dabble. Anyway, I have 3 tables that I'm trying to join in a single query. It's working, but not being displayed like I want.
One table has the idUser, strLoginID and strLoginPassword
Second table lists all file in each idSpecOrder, keyed by idSpecFile
Third table lists access for said idSpecFile, with access given based on idUser
Here is my query:
SELECT tblFU_SpecFiles.idSpecFile, tblFU_SpecFiles.idSpecOrder, tblFU_SpecFiles.strFileName,
tblFU_SpecFiles.strURL, tblFU_SpecFiles.floFileSize, tblFU_SpecFiles.StrFileDate,
tblFU_SpecFiles.StrFileType, tblFU_login.strLoginID, tblFU_SpecFileSecurity.intFileAccessLevel
FROM tblFU_login INNER JOIN
tblFU_SpecFileSecurity ON tblFU_login.idUser = tblFU_SpecFileSecurity.idUser
RIGHT OUTER JOIN
tblFU_SpecFiles ON tblFU_SpecFileSecurity.idSpecFile = tblFU_SpecFiles.idSpecFile
WHERE (tblFU_SpecFiles.idSpecOrder = 55882)
Giving me the output below:
idSpecFile idSpecOrder strFileName strURL floFileSize StrFileDate StrFileType strLoginID intFileAccessLevel
----------- ----------- --------------------------------------- --------------------------------------------------------------------------------------- --------------- ----------------------- --------------- --------------- ------------------
292 55882Architectural Vol. 1A..ZIPhttp://www.mysite.com/userfiles/94704cdc1676d0550ab7/Architectural Vol. 1A..ZIP6.431834/28/2010 13:37WinZip FileDGR1
293 55882Architectural Vol. 1B.ZIPhttp://www.mysite.com/userfiles/94704cdc1676d0550ab7/Architectural Vol. 1B.ZIP6.938164/28/2010 13:38WinZip FileNULLNULL
294 55882Architectural Vol. 2.ZIPhttp://www.mysite.com/userfiles/94704cdc1676d0550ab7/Architectural Vol. 2.ZIP2.239014/28/2010 13:39WinZip FileDGR1
321 55882Family Healthcare - MEP PDFs.ziphttp://www.mysite.com/userfiles/94704cdc1676d0550ab7/Family Healthcare - MEP PDFs.zip3.041434/28/2010 13:42WinZip FileTest1
321 55882Family Healthcare - MEP PDFs.ziphttp://www.mysite.com/userfiles/94704cdc1676d0550ab7/Family Healthcare - MEP PDFs.zip3.041434/28/2010 13:42WinZip Fileszeiler1
321 55882Family Healthcare - MEP PDFs.ziphttp://www.mysite.com/userfiles/94704cdc1676d0550ab7/Family Healthcare - MEP PDFs.zip3.041434/28/2010 13:42WinZip FileDGR1
322 55882Structural Vol. 1.ZIPhttp://www.mysite.com/userfiles/94704cdc1676d0550ab7/Structural Vol. 1.ZIP2.373464/28/2010 13:40WinZip FileNULLNULL
I would like each idSpecFile to be listed once, separated by the users with access to said file bases on the intFileAccessLevel.
This would be ideal:
idSpecFile idSpecOrder strFileName strURL floFileSize StrFileDate StrFileType DGRTestszeiler
----------- ----------- --------------------------------------- --------------------------------------------------------------------------------------- --------------- ----------------------- --------------- ------- ------- -------
292 55882Architectural Vol. 1A..ZIPhttp://www.mysite.com/userfiles/94704cdc1676d0550ab7/Architectural Vol. 1A..ZIP6.431834/28/2010 13:37WinZip File1NullNull
293 55882Architectural Vol. 1B.ZIPhttp://www.mysite.com/userfiles/94704cdc1676d0550ab7/Architectural Vol. 1B.ZIP6.938164/28/2010 13:38WinZip FileNULLNullNULL
294 55882Architectural Vol. 2.ZIPhttp://www.mysite.com/userfiles/94704cdc1676d0550ab7/Architectural Vol. 2.ZIP2.239014/28/2010 13:39WinZip File1NullNull
321 55882Family Healthcare - MEP PDFs.ziphttp://www.mysite.com/userfiles/94704cdc1676d0550ab7/Family Healthcare - MEP PDFs.zip3.041434/28/2010 13:42WinZip File111
322 55882Structural Vol. 1.ZIPhttp://www.mysite.com/userfiles/94704cdc1676d0550ab7/Structural Vol. 1.ZIP2.373464/28/2010 13:40WinZip FileNULLNullNULL
Is this even possible? Keep in mind that there are several idSpecOrder with differing users access each.
If you'd like to take a stab, grab the tables and same data below for testing:
--== if tables already exists, drop them
IF OBJECT_ID('tblFU_login', 'U') IS NOT NULL
DROP TABLE tblFU_login
IF OBJECT_ID('tblFU_Spec', 'U') IS NOT NULL
DROP TABLE tblFU_Spec
IF OBJECT_ID('tblFU_SpecFiles', 'U') IS NOT NULL
DROP TABLE tblFU_SpecFiles
IF OBJECT_ID('tblFU_SpecFileSecurity', 'U') IS NOT NULL
DROP TABLE tblFU_SpecFileSecurity
--== now create our tables
CREATE TABLE [dbo].[tblFU_login]
(
[idUser] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[strLoginID] [varchar](50) NULL,
[strLoginPassword] [varchar](50) NULL
)
CREATE TABLE [dbo].[tblFU_Spec]
(
[idSpecOrder] [int] IDENTITY(55555,1) PRIMARY KEY CLUSTERED,
[strContact] [varchar](150) NULL,
[fk_UserName] [varchar](50) NULL,
[intStatus] [int] NULL,
[halfhash] [varchar](25) NULL,
[strProjectName] [varchar](50) NULL
)
CREATE TABLE [dbo].[tblFU_SpecFiles]
(
[idSpecFile] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[idSpecOrder] [int] NOT NULL,
[strFileName] [varchar](max) NULL,
[strURL] [varchar](max) NULL,
[floFileSize] [float] NULL,
[StrFileDate] [varchar](50) NULL,
[StrFileType] [varchar](50) NULL
)
CREATE TABLE [dbo].[tblFU_SpecFileSecurity]
(
[idFileSpecSec] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[idSpecFile] [int] NOT NULL,
[idSpecOrder] [int] NOT NULL,
[idUser] [int] NOT NULL,
[intFileAccessLevel] [int] NOT NULL
)
--== Allow inserts into the IDENTITY column for tblFU_login
SET IDENTITY_INSERT tblFU_login ON
--== Insert sample data into the table tblFU_login
INSERT INTO tblFU_login
(idUser, strLoginID, strLoginPassword)
SELECT '3','Joemamma','joe' UNION ALL
SELECT '4','Test','miami' UNION ALL
SELECT '5','szeiler','cdm' UNION ALL
SELECT '11','DGR','DGR'
--== Reset IDENTITY back OFF for table tblFU_login
SET IDENTITY_INSERT tblFU_login OFF
--== Allow inserts into the IDENTITY column for tblFU_Spec
SET IDENTITY_INSERT tblFU_Spec ON
--== Insert sample data into the table tblFU_Spec
INSERT INTO tblFU_Spec
(idSpecOrder, strContact, fk_UserName, intStatus, halfhash, strProjectName)
SELECT '55882','Steve','Joemamma','1','94704cdc1676d0550ab7','Family Healthcare' UNION ALL
SELECT '55884','Test User 2','Joemamma','1','6596d9eed80a0087f72d','Test Project 2' UNION ALL
SELECT '55886','Dwight or Terry','Joemamma','1','69d4f11760c007159a47','Community School Collapsed Roof Repair' UNION ALL
SELECT '55887','Steve','szeiler','2','a1d2641b73606cced453','Family Healthcare 2' UNION ALL
SELECT '55889','Rusty Profitt','szeiler','1','851b45c0fcc37fcffd11','828521'
--== Reset IDENTITY back OFF for table tblFU_Spec
SET IDENTITY_INSERT tblFU_Spec OFF
--== Allow inserts into the IDENTITY column for tblFU_SpecFiles
SET IDENTITY_INSERT tblFU_SpecFiles ON
--== Insert sample data into the table tblFU_SpecFiles
INSERT INTO tblFU_SpecFiles
(idSpecFile, idSpecOrder, strFileName, strURL, floFileSize, StrFileDate, StrFileType)
SELECT '292','55882','Architectural Vol. 1A..ZIP','http://www.mysite.com/userfiles/94704cdc1676d0550ab7/Architectural Vol. 1A..ZIP','6.43183','4/28/2010 1:37:39 PM','WinZip File' UNION ALL
SELECT '293','55882','Architectural Vol. 1B.ZIP','http://www.mysite.com/userfiles/94704cdc1676d0550ab7/Architectural Vol. 1B.ZIP','6.93816','4/28/2010 1:38:44 PM','WinZip File' UNION ALL
SELECT '294','55882','Architectural Vol. 2.ZIP','http://www.mysite.com/userfiles/94704cdc1676d0550ab7/Architectural Vol. 2.ZIP','2.23901','4/28/2010 1:39:27 PM','WinZip File' UNION ALL
SELECT '321','55882','Family Healthcare - MEP PDFs.zip','http://www.mysite.com/userfiles/94704cdc1676d0550ab7/Family Healthcare - MEP PDFs.zip','3.04143','4/28/2010 1:42:31 PM','WinZip File' UNION ALL
SELECT '322','55882','Structural Vol. 1.ZIP','http://www.mysite.com/userfiles/94704cdc1676d0550ab7/Structural Vol. 1.ZIP','2.37346','4/28/2010 1:40:09 PM','WinZip File'
--== Reset IDENTITY back OFF for table tblFU_SpecFiles
SET IDENTITY_INSERT tblFU_SpecFiles OFF
--== Allow inserts into the IDENTITY column for tblFU_SpecFileSecurity
SET IDENTITY_INSERT tblFU_SpecFileSecurity ON
--== Insert sample data into the table tblFU_SpecFileSecurity
INSERT INTO tblFU_SpecFileSecurity
(idFileSpecSec, idSpecFile, idSpecOrder, idUser, intFileAccessLevel)
SELECT '4','321','55882','4','1' UNION ALL
SELECT '5','321','55882','5','1' UNION ALL
SELECT '6','321','55882','11','1' UNION ALL
SELECT '7','292','55882','11','1' UNION ALL
SELECT '8','294','55882','11','1'
--== Reset IDENTITY back OFF for table tblFU_SpecFileSecurity
SET IDENTITY_INSERT tblFU_SpecFileSecurity OFF
Thanks for your consideration!
June 2, 2010 at 6:23 pm
Very possible, and not that hard to do.
Read the "Cross Tabs and Pivot Tables" Part 1 and 2 articles (click the links in my signature).
BTW, thanks for posting the table DDL/DML! It does make it a lot easier for people to help you.
And, here's how to do it:
SELECT tblFU_SpecFiles.idSpecFile,
tblFU_SpecFiles.idSpecOrder,
tblFU_SpecFiles.strFileName,
tblFU_SpecFiles.strURL,
tblFU_SpecFiles.floFileSize,
tblFU_SpecFiles.StrFileDate,
tblFU_SpecFiles.StrFileType,
DGR = MAX(CASE WHEN tblFU_login.strLoginID = 'DGR' THEN intFileAccessLevel ELSE NULL END),
Test = MAX(CASE WHEN tblFU_login.strLoginID = 'Test' THEN intFileAccessLevel ELSE NULL END),
szeiler = MAX(CASE WHEN tblFU_login.strLoginID = 'szeiler' THEN intFileAccessLevel ELSE NULL END)
FROM tblFU_login
JOIN tblFU_SpecFileSecurity
ON tblFU_login.idUser = tblFU_SpecFileSecurity.idUser
RIGHT JOIN tblFU_SpecFiles
ON tblFU_SpecFileSecurity.idSpecFile = tblFU_SpecFiles.idSpecFile
WHERE (tblFU_SpecFiles.idSpecOrder = 55882)
GROUP BY tblFU_SpecFiles.idSpecFile,
tblFU_SpecFiles.idSpecOrder,
tblFU_SpecFiles.strFileName,
tblFU_SpecFiles.strURL,
tblFU_SpecFiles.floFileSize,
tblFU_SpecFiles.StrFileDate,
tblFU_SpecFiles.StrFileType
Note that if you want to create a separate column for EACH strLoginID beyond the three specified, then you definitely want to read Part 2, especially the Dynamic Cross-Tab section!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2010 at 10:15 am
Perfect, this is exactly what I needed! I will need to review your Dynamic Cross-Tab section as strLoginID with access will vary widely based on the idSpecOrder.
Thanks for working on the query, I really appreciate it.
June 3, 2010 at 10:26 am
Alan Armstrong-249094 (6/3/2010)
Perfect, this is exactly what I needed! I will need to review your Dynamic Cross-Tab section as strLoginID with access will vary widely based on the idSpecOrder.Thanks for working on the query, I really appreciate it.
No problem, glad to help out.
Those excellent CROSS-TAB/PIVOT articles aren't by me, they're by Jeff Moden.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply