0

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

Improve this question
1

2 Answers 2

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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; ```
Improve this answer

2 Comments

Yes but there is a 'WHERE' so the result will be the same ? Shouldn't a 'JOIN' be after the 'WHERE' ? I'm new to SQL sorry
No the where belongs after the JOIN statements. The results should be like you want them to.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.