SQLTeam.com | Weblogs | Forums

SQL Join on the second table

#1

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.

#2
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.

#3

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 :flushed:

#4

[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.

#5

'Does not want to disclose' is not a race. Then all over your code you will have to account for this 'condition'

#6

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
#7

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?

#8

search in this forum

select (datediff(month,'1914-07-28',getdate())+1)/12 as age

#9

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.

#10

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

#11

depends, just like you saw with the race table does every race have race_info?

#12

there is a col in the all_people that
'is_declined_race' and some are true.

#13

^^ see this question ^^

#14

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.

#15

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

#16

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

#17

Like I said before if you do an inner join from race to race_info table you will only get people with race data.