June 28, 2007 at 2:56 am
I have a locking mechanism in place for a web application, but when i run the sql below
to get a list of users which shows an id for the person who has currently locked the record
i get a duplicate entry when there is two lock entries in the record_locks table for a particular record - which is unlikely to happen but could, eg record set returned
fullname, locked by user id
billy fraser, null
jim smith, 2
jim smith, 38
ally brown, null
Here i only want to return one instance of jim smith - if that is possible?
Many thanks in advance.....
SELECT TOP (100) PERCENT dbo.USERS_LOGIN.UL_CONTACT_SURNAME + ', ' + dbo.USERS_LOGIN.UL_CONTACT_FIRSTNAME AS FULLNAME,
dbo._RECORD_LOCKS.RL_LOCKED_BY_USER_ID
FROM dbo.USERS_LOGIN LEFT OUTER JOIN
dbo._RECORD_LOCKS ON dbo.USERS_LOGIN.UL_LOGIN_GUID = dbo._RECORD_LOCKS.RL_LOCKED_RECORD_GUID
June 28, 2007 at 3:02 am
It's possible, but a little tricky. which of the rows that have the full name of Jim smith would you want returned?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 28, 2007 at 3:13 am
either one really it doesnt matter - as long i can just show that the record jim smith is locked by someone.
it is an unlikely scenario, but i need to cater for it obviously....
cheers
June 28, 2007 at 3:23 am
You could start with a distinct list of RL_LOCKED_RECORD_GUID from _RECORD_LOCKS and then outer join this list back to the USERS_LOGIN table.
You'd need to do a bit of tidying up but ultimately this could give you a complete list of user names with a second column containing a value such as 'True' to indicate that the user has a lock (dependant on their guid being contained in the lock table, regardless of how many time it appears).
June 28, 2007 at 3:26 am
OK, let's give this a try...
SELECT
dbo.USERS_LOGIN.UL_CONTACT_SURNAME + ', ' + dbo.USERS_LOGIN.UL_CONTACT_FIRSTNAME AS FULLNAME, LockedBy
FROM dbo.USERS_LOGIN LEFT OUTER JOIN
(SELECT RL_LOCKED_RECORD_GUID, MIN(RL_LOCKED_BY_USER_ID) AS LockedBy FROM dbo._RECORD_LOCKS GROUP BY RL_LOCKED_RECORD_GUID) RecordLocks
ON dbo.USERS_LOGIN.UL_LOGIN_GUID = RecordLocks.RL_LOCKED_RECORD_GUID
See if this works please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 28, 2007 at 3:29 am
that works perfectly!
thanks a lot for your help - much appreciated....
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply