• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

SQL view return results for coded values

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
guswebb
Asked:
guswebb
  • 2
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
guswebbAuthor Commented:
Perfect, thanks!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
guswebbAuthor Commented:
ValueID is an autoid field. I will give the code a try without that element in place, thanks.
0

Featured Post

Free tool for managing users' photos in Office 365

Easily upload multiple users’ photos to Office 365. Manage them with an intuitive GUI and use handy built-in cropping and resizing options. Link photos with users based on Azure AD attributes. Free tool!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now