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 |
4 | Patrick |
5 | Bob |
6 | Carlos |
7 | Mandy |
affect :
id | fk_field | fk_person |
---|---|---|
1 | 2 | 1 |
2 | 1 | 2 |
3 | 3 | 3 |
With this query :
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
WHERE f.f_end < 21 AND f.f_start > 7;
I'm getting this result :
p_name | f_start | f_end |
---|---|---|
John | 10 | 20 |
But I would like to have the persons who are not in affect too, meaning that they are available. It includes that there will be empty values like this :
p_name | f_start | f_end |
---|---|---|
John | 10 | 20 |
Patrick | ||
Bob | ||
Carlos | ||
Mandy |
Would that be possible ? thanks
CodePudding user response:
You need to use LEFT JOIN
instead of INNER JOIN
and also need to specify the condition in the LEFT JOIN
itself. The condition in WHERE
clause is given to get your expected result. I.e. to include person which either matches with the field table or doesn't available in the affect table:
SELECT p.p_name, f.f_start, f.f_end
FROM person p
LEFT JOIN affect a ON a.fk_person = p.p_id
LEFT JOIN field f ON f.f_id = a.fk_field AND f.f_end < 21 AND f.f_start > 7
WHERE f.f_id IS NOT NULL
OR a.id IS NULL
Output:
p_name | f_start | f_end |
---|---|---|
John | 10 | 20 |
Patrick | ||
Bob | ||
Carlos | ||
Mandy |
See this fiddle
CodePudding user response:
Use a left join:
FROM person p
LEFT JOIN affect a ON a.fk_person = p.p_id
INNER JOIN field f ON f.f_id = a.fk_field
WHERE f.f_end < 21 AND f.f_start > 7; ```