0

I want all record of user table but query return only matched records in both table.I am showing user attendance of current month whether user attendance exist or not in attendance table.

SELECT MAX(a1.attendance_id) atid,
a1.attendance_user_id, 
CONCAT(u1.user_firstname, ' ', u1.user_lastname) user_name, 
YEAR(attendance_date) at_year, 
MONTH(attendance_date) at_month, 
GROUP_CONCAT(DAY(attendance_date ) ORDER BY attendance_date) at_date, 
GROUP_CONCAT(attendance_type ORDER BY attendance_date) at_type 
FROM user u1 LEFT JOIN attendance a1 ON u1.user_id = a1.attendance_user_id 
WHERE u1.user_status = 'Active' AND DATE(a1.attendance_date) 
BETWEEN DATE_FORMAT(NOW() ,'%Y-%m-01') AND CURDATE() 
GROUP BY a1.attendance_user_id, YEAR(attendance_date), MONTH(attendance_date) ORDER BY atid DESC limit 0, 25
r92
  • 2,800
  • 2
  • 19
  • 24
ashish
  • 5
  • 1
  • 5

1 Answers1

0

You need to put DATE(a1.attendance_date) BETWEEN DATE_FORMAT(NOW() ,'%Y-%m-01') AND CURDATE() condition in your ON Clause

SELECT 
    MAX(a1.attendance_id) atid,
    a1.attendance_user_id, 
    CONCAT(u1.user_firstname, ' ', u1.user_lastname) user_name, 
    YEAR(attendance_date) at_year, 
    MONTH(attendance_date) at_month, 
    GROUP_CONCAT(DAY(attendance_date ) ORDER BY attendance_date) at_date, 
    GROUP_CONCAT(attendance_type ORDER BY attendance_date) at_type 
FROM user u1 LEFT JOIN attendance a1 ON u1.user_id = a1.attendance_user_id 
AND DATE(a1.attendance_date) BETWEEN DATE_FORMAT(NOW() ,'%Y-%m-01') AND CURDATE() 
WHERE u1.user_status = 'Active' 
GROUP BY a1.attendance_user_id, YEAR(attendance_date), MONTH(attendance_date) 
ORDER BY atid DESC limit 0, 25
Fahmi
  • 37,315
  • 5
  • 22
  • 31
  • but still not return all users.Only return those users who make attendance but I want all users whether make attendance or not . – ashish May 14 '19 at 09:29