SQL select with three tables and foreign keys

user_15506660 0
Viewed 218

I have three tables :

field:

f_id f_start f_end
1 10 20
2 15 25
3 5 10

person :

p_id p_name
1 Roger
2 John
3 Alicia

affect :

id fk_field fk_person
1 2 1
2 1 2
3 3 3

And I would like to select the dates and the names associated to. Like this

p_name f_start f_end
Roger 15 25
John 10 20
Alicia 5 10

I'm new to SQL and I don't know if i have to use JOIN or not... Thanks

select join foreign-keys sql database
1 Answers

You must join all 3 tables on their related columns:

SELECT p.p_name, f.f_start, f.f_end
FROM person p
INNER JOIN affect a ON a.fk_person = p.p_id
INNER JOIN field f ON f.f_id = a.fk_field;

Depending on your requirement you may need LEFT instead of INNER joins, but for this sample data the INNER joins will do.

user_104988280
Related