SQL Help Needed - 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)

SQL Help Needed

Author

Topic

Nexzus
Starting Member

14 Posts

Posted - 2009-05-28 : 19:32:48

Hello,

We're starting off a data migration from one Enterprise Document Management System to another. I need to find all documents in the old system that are only owned by people who are no longer here, ie there's only one entry for a document in the below ACL Table

Relevant Pseudo Table Structure
Users:
TEXT Username
INT IsActive (1 = Still here, 0 = is gone.)

Documents:
PRIMARY KEY INT DocumentID

AccessControlList:
DocumentID
Username

System is SQL Server 2005 on Win2K3.

Any help is appreciated.

***********

Edit, I should probably add that, no, there's no functionality for this in the old system.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-05-28 : 21:20:09

Select * from
Users a
Inner join
AccessControlList b
on a.Username = b.Username
inner Join
Documents c
on b.DocumentID = c.DocumentID
where
a.isactive = 0

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Nexzus
Starting Member

14 Posts

Posted - 2009-05-29 : 11:44:10

Thank you. That looks like it will grab any document that a disabled user has access to, though. I was needing if a document only has one ACL record associated with it, and that one ACL record has a disabled user. What I need (I believe) is the correct form of this:

SELECT Document.DocumentID,
COUNT(ACL.DocumentID) AS DocumentACLRecordCount
FROM Document
INNER JOIN ACL ON Document.DocumentID = ACL.DocumentID
INNER JOIN Users ON ACL.UserName = Users.UserName
GROUP BY ACL.DocumentID
HAVING DocumentACLRecordCount = 1
WHERE
Users.IsActive = 0;

I don't know of a way to have the result of an aggregate function as a comparison in having or where clause.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-30 : 02:58:09

do you mean this?


SELECT Document.DocumentID,
COUNT(ACL.DocumentID) AS DocumentACLRecordCount
FROM Document
INNER JOIN ACL ON Document.DocumentID = ACL.DocumentID
INNER JOIN Users ON ACL.UserName = Users.UserName
WHERE Users.IsActive = 0
GROUP BY ACL.DocumentID
HAVING COUNT(ACL.DocumentID)= 1;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources