raymurphy
asked on
Changing a query to decode numerical reference data
Have the following query :
SELECT DISTINCT
cc.ClientID,
cc.PriorityKey,
cc.ReliabilityKey,
pty.ClientFullName,
s.Forename,
s.Surname,
rl.DataLookupKey AS RoleId,
rl.DataDisplayName AS RoleName
FROM ClientDetails cc
INNER JOIN CaseClients cs
ON cs.CaseClientsID = cc.ClientID
INNER JOIN ClientNames pty
ON pty.ClientNameID = cs.CaseClientsID
INNER JOIN StaffDetails sd
ON sd.ClientID = cc.ClientID
INNER JOIN UserList s ON sd.UserListID = s.UserListID
INNER JOIN ReferenceData rl ON sd.SecurityRoleKey = rl.DataLookupKey
Above query works OK (backend database is SQL 2005 just migrated from SQL 2000) - problem is that the values retrieved for PriorityKey and ReliabilityKey are numerics and that has been fine for our application up until now.
The numeric values for these two fields can be decoded by looking at DataLookupKey on the ReferenceData table (matching on DataLookupKey) and taking the text description from DataDisplayName column on the ReferenceData table.
We now need to amend the above query to show the text descriptions for PriorityKey and ReliabilityKey as well as the existing numeric values, so how should I amend the above query to do that ?
(As an aside given that we have now moved to SQL 2005, I wasn't sure whether the query would
benefit from using a CTE/Common Table Expression ?) ...
SELECT DISTINCT
cc.ClientID,
cc.PriorityKey,
cc.ReliabilityKey,
pty.ClientFullName,
s.Forename,
s.Surname,
rl.DataLookupKey AS RoleId,
rl.DataDisplayName AS RoleName
FROM ClientDetails cc
INNER JOIN CaseClients cs
ON cs.CaseClientsID = cc.ClientID
INNER JOIN ClientNames pty
ON pty.ClientNameID = cs.CaseClientsID
INNER JOIN StaffDetails sd
ON sd.ClientID = cc.ClientID
INNER JOIN UserList s ON sd.UserListID = s.UserListID
INNER JOIN ReferenceData rl ON sd.SecurityRoleKey = rl.DataLookupKey
Above query works OK (backend database is SQL 2005 just migrated from SQL 2000) - problem is that the values retrieved for PriorityKey and ReliabilityKey are numerics and that has been fine for our application up until now.
The numeric values for these two fields can be decoded by looking at DataLookupKey on the ReferenceData table (matching on DataLookupKey) and taking the text description from DataDisplayName column on the ReferenceData table.
We now need to amend the above query to show the text descriptions for PriorityKey and ReliabilityKey as well as the existing numeric values, so how should I amend the above query to do that ?
(As an aside given that we have now moved to SQL 2005, I wasn't sure whether the query would
benefit from using a CTE/Common Table Expression ?) ...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER