Home > Mobile >  SQL get rows being not in JOIN
SQL get rows being not in JOIN

Time:05-19

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



  • Related