Solved

SQL - Select Description based on ID

Posted on 2011-09-29
6
257 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 38

Accepted Solution

by:
Aaron Tomosky earned 167 total points
Comment Utility
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
Comment Utility
Ok, so the description from tblRace would go after SELECT?   Like:

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

Expert Comment

by:Aaron Tomosky
Comment Utility
Select m.*, r.description from tblmain m
Left join tblrace r on m.race_id = r.id
0
Highfive Gives IT Their Time Back

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!

 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 166 total points
Comment Utility

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
Comment Utility
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
Comment Utility
Thanks a bunch!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

763 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

9 Experts available now in Live!

Get 1:1 Help Now