Need to join but on a secondary table

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.

Answer

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

Advertisement

Answer