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 - Select Description based on ID

Posted on 2011-09-29
6
262 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
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 59

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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

790 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