Need to join but on a secondary table

Question

I have this one view all_people_expanded_view that has all the data needed except 1. The race of client is in race table. But the description is in race_info. I need to join on race on people_id, but really need then to join race on race_info on column race_info_id and then get description. I am stuck on how to make this connection.

select a.full_name, a.dob, a.gender, a.ethnicity, c.race_info_id 
from all_people_expanded_view a inner join
     race c
     on a.people_id = c.people_id

this would be fine but it only has race_info_id and not the description which is in the race_info_id table.

score 0 · Answer 1 · answered May 14 '19 at 21:15

0

select a.full_name, a.dob, a.gender, a.ethnicity, c.race_info_id , ri.description from all_people_expanded_view a inner join race c on a.people_id = c.people_id inner join race_info ri on ri.race_info_id = c.race_info_id

answered May 14 '19 at 21:15

Pinkab

21
4

I wanted to know if there is a formula or way to get the dob to be as the age ? – Pinkab May 14 '19 at 21:24
Here you go: https://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate – Eric Brandt May 16 '19 at 15:58

score 0 · Accepted Answer · answered May 14 '19 at 21:15

Is this what you're looking for?

select 
  a.full_name, 
  a.dob, 
  a.gender, 
  a.ethnicity, 
  c.race_info_id,
  ri.description
from 
  all_people_expanded_view a 
inner join
  race c
    on a.people_id = c.people_id
left join --Maybe an inner join, depending on your data
  race_info ri
    on ri.race_info_id = c.race_info_id

Need to join but on a secondary table

2 Answers2