Solved

SQL - Select Description based on ID

Posted on 2011-09-29
6
264 Views
Last Modified: 2012-05-12
Hello,

I have a database with a main table representing people and a bunch of support tables which represent demographic characteristics of those people.  The main table has ID#'s for corresponding descriptions in the support tables and I need to figure out how to translate the ID's to Descriptions.  

For instance, the main table (tblMain) has a field "Race_ID" which corresponds to the "ID" column in the "Race" support table.  (e.g. 1|White, 2|Black, 3|Asian, etc).  

I'm trying to figure out how to get the "Race_Description" field from "tblRace" where the "Race_ID" field in "tblMain" matches the "ID" field in "tblRace".

How can I do this?  

Thanks!
0
Comment
Question by:ttist25
[X]
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
6 Comments
 
LVL 39

Accepted Solution

by:
Aaron Tomosky earned 167 total points
ID: 36839805
This sounds like a basic join to me.
Select * from tblmain m
Left join tblrace r on m.race_id = r.id

Change the * to the columns you want
0
 
LVL 1

Author Comment

by:ttist25
ID: 36842529
Ok, so the description from tblRace would go after SELECT?   Like:

SELECT Description from tblmain m
LEFT JOIN etc.?
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 36848049
Select m.*, r.description from tblmain m
Left join tblrace r on m.race_id = r.id
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 166 total points
ID: 36862250

Try this

select M.* , R.Race_Description
From tblMain M inner join tblRace R on M.Race_ID = R.ID
0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 167 total points
ID: 36879843
It appears you are getting plenty of help on the query itself, so I will specifically address some explanation. Yes, the name of the columns you want should go after the SELECT and before the FROM. I would avoid using * and instead explicitly define each column you want to see. It is a better practice, but in this instance if you are adding a number of "Description" columns, i.e., they all have the same name, you will want to explicitly identify each as shown by pratima_mcs and aarontomosky using the table name or alias (e.g., R is alias for tblRace). Just as they have both aliased the table, you will want to alias the columns.

SELECT R.Description AS [Race], X.Description AS SomeAlias, ...

The choice of LEFT OUTER JOIN versus INNER JOIN will be based on if you have any rows where a specific ID may be NULL. In that case, with an OUTER JOIN, you will get NULL for that description but will still see the row which may have data in another description table versus an INNER JOIN will exclude that row totally. With INNER JOINs, you will only see the rows that have ALL the ids; therefore, this choice requires an understanding of your data and what you ultimately want as the result.

Hope that helps!
0
 
LVL 1

Author Closing Comment

by:ttist25
ID: 36892613
Thanks a bunch!
0

Featured Post

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

738 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