I have the following MySQL tables:
[users]
| id | name |
|----|---------|
| 1 | John |
| 2 | Anna |
| 3 | Peter |
[times]
| user_ID | date | time |
|---------|------------|--------|
| 1 | 2020-03-20 | 07:00 |
| 1 | 2020-03-21 | 08:00 |
| 3 | 2020-03-22 | 09:00 |
my query look like:
SELECT name, date, time
FROM users
INNER JOIN times ON times.user_ID = users.id
WHERE date = '2020-03-22';
what i get is:
| name | date | time |
|---------|------------|--------|
| Peter | 2020-03-22 | 09:00 |
what i want is:
| name | date | time |
|---------|------------|--------|
| John | | |
| Anna | | |
| Peter | 2020-03-22 | 09:00 |
is there a way to join non existent lines (not fields!) in the times table with the users table?
解决方案
use left join and move where clause to on:
SELECT name, date, time
FROM users
left JOIN times ON times.user_ID = users.id and date = '2020-03-22';