[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL - Select Description based on ID

Posted on 2011-09-29
6
Medium Priority
?
269 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 668 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 664 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 668 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

656 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