CODEWITHSUNDEEP
mysqlsqljoinleft-join
sunny
sunny

Reputation: 1593

join two table return blank if not found MYSQL

I am facing an issue in the MySQL query. I use Left join in my query to join two tables but I failed to get my expected result.

Let's suppose my user table is

Users Table

Id  Name
1   User 1
2   User 2
3   User 3
4   User 4
5   User 5
6   User 6
7   User 7

Attendance Table

Id  user_id date
1   1   2020-03-30
2   2   2020-03-30
3   3   2020-03-30
4   4   2020-03-30
5   7   2020-03-30
6   1   2020-03-31
7   3   2020-03-31
8   5   2020-03-31
9   6   2020-03-31
10  1   2020-04-01
11  2   2020-04-01
12  3   2020-04-01

My expected Result

1   User 1  2020-04-01
2   User 2  2020-04-01
3   User 3  2020-04-01
4   User 4  NULL
5   User 5  NULL
6   User 6  NULL
7   User 7  NULL

Here is my query

SELECT og_users.id, og_users.display_name, pacra_attendance.date, pacra_attendance.log_in_time
FROM og_users

LEFT JOIN pacra_attendance
ON og_users.id = pacra_attendance.user_id

WHERE og_users.is_active = 1 AND pacra_attendance.date = '2020-04-01'

Please help me out.

Thank you

Upvotes: 0

Views: 528

Answers (1)

Sebastian Brosch
Sebastian Brosch

Reputation: 43564

You can use this:

SELECT og_users.id, og_users.display_name, pacra_attendance.date, pacra_attendance.log_in_time
FROM og_users LEFT JOIN pacra_attendance
  ON og_users.id = pacra_attendance.user_id AND pacra_attendance.date = '2020-04-01'
WHERE og_users.is_active = 1

demo on dbfiddle.uk

You can move the date condition from WHERE to LEFT JOIN. Using the date condition on the WHERE is like using a INNER JOIN.

Upvotes: 2

Related Questions