Solved

SQL view return results for coded values

Posted on 2013-12-12
4
244 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Marketers need statistics and metrics like everybody else needs oxygen. In this article we explain how to enable marketing campaign statistics for Microsoft Exchange mail.
In this video we show how to create a Distribution Group 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 Recipients >>…
In this video we show how to create a Resource Mailbox in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: Navigate to the Recipients >> Resources tab.: "Recipients" is our default selection …

708 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

17 Experts available now in Live!

Get 1:1 Help Now