Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

SQL - Select Description based on ID

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
ttist25
Asked:
ttist25
3 Solutions
 
Aaron TomoskySD-WAN SimplifiedCommented:
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
 
ttist25Author Commented:
Ok, so the description from tblRace would go after SELECT?   Like:

SELECT Description from tblmain m
LEFT JOIN etc.?
0
 
Aaron TomoskySD-WAN SimplifiedCommented:
Select m.*, r.description from tblmain m
Left join tblrace r on m.race_id = r.id
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Pratima PharandeCommented:

Try this

select M.* , R.Race_Description
From tblMain M inner join tblRace R on M.Race_ID = R.ID
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
ttist25Author Commented:
Thanks a bunch!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now