Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL view return results for coded values

Posted on 2013-12-12
4
Medium Priority
?
262 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 66

Accepted Solution

by:
Jim Horn earned 2000 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 66

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A method of moving multiple mailboxes (in bulk) to another database in an Exchange 2010/2013/2016 environment...
Are you working to mount the dismounted Exchange 2013 database? Then the best course of action is to analyze the causes of Database issue, their probable solutions and decide for the appropriate course of action.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

584 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