Solved

SQL view return results for coded values

Posted on 2013-12-12
4
250 Views
Last Modified: 2013-12-12
I have coded values in table A and table B however I need to view these easily in a view called MyView, as per below. There are millions of rows in table A and table B so I have trimmed this down significantly as per below snapshot.

table A            
ValueID      field      value
1      type      Compact
2      type      Convertible
3      type      Estate
4      type      Less than 1 year old
5      age      1 - 2 years old
6      age      2 - 3 years old
7      age      3 - 4 years old
8      age      4 - 5 years old
            
table B            
userID      type      age
251846      1      5
251847      3      8
            
MyView            
UserID      Type      Age
251846      Compact      1 - 2 years old
251847      Estate      4 - 5 years old
0
Comment
Question by:guswebb
  • 2
  • 2
4 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39714184
CREATE VIEW my_view AS

/*
Enter some amazingly helpful code comments here
*/

SELECT b.UserID, atype.Value as Type, aage.value as Age
FROM TableB b
   JOIN TableA atype ON b.type = atype.ValueID AND atype.field = 'type'
   jOIN TableA aage ON b.age= aage.ValueID AND aage.field = 'age'

GO
0
 
LVL 9

Author Comment

by:guswebb
ID: 39714243
Perfect, thanks!
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39714275
Thanks for the grade.  Good luck with your project.  -Jim

btw, in Table A, are the ValueID numbers unique?  
If yes, then you can lose the '... and a.field='something' part, especially if field is not part of an index, which would mean the query would do a slower scan instead of a seek.
If no, then you'll need it.
0
 
LVL 9

Author Comment

by:guswebb
ID: 39714396
ValueID is an autoid field. I will give the code a try without that element in place, thanks.
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
In this video we show how to create an Address List in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Organization >> Ad…
In this video we show how to create a mailbox database in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Servers >> Data…

773 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