CODEWITHSUNDEEP
mysqlsqljoin
3x3cut0r
3x3cut0r

Reputation: 21

MySQL SELECT JOIN with empty row returns

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?

Upvotes: 0

Views: 91

Answers (2)

Jens
Jens

Reputation: 69440

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

Upvotes: 1

Barmar
Barmar

Reputation: 780723

Use LEFT JOIN. And then you need to put the restrictions on the second table into the ON clause.

SELECT name, date, time 
FROM users 
LEFT JOIN times ON times.user_ID = users.id AND date = '2020-03-22';

Upvotes: 1

Related Questions