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, b.race_info_id from all_people_expanded_view a inner join race c on a.people_id = c.people_id.
select
a.full_name, a.dob, a.gender, a.ethnicity, b.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
If you always need the race description, you can edit the code for your view and add the joins there.
I avoid inner joins on views. What if a person does not want to identify their race then they get excluded unless you have a no race race data in the race table 
[quote="JamesK, post:2, topic:15664"]
select a.full_name, a.dob, a.gender, a.ethnicity, b.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
[/quote] We do have a 'does not want to disclose' button on the form, but i have to see how it's coded.
'Does not want to disclose' is not a race. Then all over your code you will have to account for this 'condition'
You don't need to post the code for the view. The join would be very similar to what I posted already.
BTW, I concur with @yosiasz comment regarding inner join unless you know that it is guaranteed that there is a row in the race table for every row in the all_people_expanded_view. In cases where the race is not known or disclosed the race_info should have a row for that. If that cannot be guaranteed, change the joins to LEFT join, and present the data as COALESCE(ri.description,'???')
1 Like
I have to check that. SO far it looks like everyone has entered the race.
Let me ask you, is there a way to show the person's age like take the DOB and calculate what is the age?
search in this forum
select (datediff(month,'1914-07-28',getdate())+1)/12 as age
Oh you're right. the table race is only populated with those who did fill out race.
I have to do that left outer join.
Do I need the left outer join on both of the second tables?
select
a.full_name, a.dob, a.gender, a.ethnicity, c.race_info_id, a.is_declined_race, ri.description
from
all_people_expanded_view a
Left outer join race c on a.people_id = c.people_id
inner join race_info ri on ri.race_info_id = c.race_info_id
depends, just like you saw with the race table does every race have race_info?
there is a col in the all_people that
'is_declined_race' and some are true.
^^ see this question ^^
No. The table race has only people who have answer to that. But the otthers we can see in col Is_declined_race. But the initial join has to include the ones who do not have race.
You asked about left join in the second tables
I am saying it depends if all races have race-info then no you do not need left join
the all_people is all of our clients. THis table does not have race. This we get in race table. But not all clients have given their race. But no matter what I do I am only getting those with race.
because I think that face_Info table only has those who did give their race. I am not coding something correctly, i switched the order of the first 2 tables but i think the third one is the problem.
select
c.full_name, c.dob, c.gender, c.ethnicity, a.race_info_id, c.is_declined_race, ri.description
from
all_people_expanded_view c
Left outer join race a on a.people_id = c.people_id
inner join race_info ri on ri.race_info_id = a.race_info_id
Like I said before if you do an inner join from race to race_info table you will only get people with race data.