Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL view return results for coded values

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Utilizing an array to gracefully append to a list of EmailAddresses
In-place Upgrading Dirsync to Azure AD Connect
how to add IIS SMTP to handle application/Scanner relays into office 365.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

829 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