Question: 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

Answer: 
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](https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=6abd4e0116f0f521c7ce15ff47508a07)
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; ```

```