Solved

Changing a query to decode numerical reference data

Posted on 2008-06-19
2
235 Views
Last Modified: 2010-03-19
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 ?) ...



0
Comment
Question by:raymurphy
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
If I understood the problem correctly, you need a second JOIN to ReferenceData:
SELECT DISTINCT
cc.ClientID, 
cc.PriorityKey,
cc.ReliabilityKey,
pty.ClientFullName,
s.Forename,
s.Surname,
rl.DataLookupKey AS RoleId,        
rl.DataDisplayName AS RoleName
r2.DataLookupKey AS RoleId2,        
r2.DataDisplayName AS RoleName2            
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               
INNER JOIN ReferenceData r2 ON sd.ReliabilityKey = r2.DataLookupKey              

Open in new window

0
 

Author Comment

by:raymurphy
Comment Utility
Works fine - thanks (hadn't realised it was simple as adding another join ...)
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now