SQL get rows being not in JOIN-CodePudding

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; ```