filter data

SELECT a.id, a.photo, a.name, a.jobType, a.age, a.expSalary, a.religion, a.prefCity FROM candidates a LEFT JOIN candidate_subscribed_packages b ON b.candId = a.id AND b.isExpired = 'N' WHERE a.isApproved = 'Y' AND a.isHired = 'N'

get data

SELECT a.id, a.photo, a.name, a.jobType, a.age, a.expSalary, a.religion, a.prefCity FROM candidates a LEFT JOIN candidate_subscribed_packages b ON b.candId = a.id AND b.isExpired = 'N' WHERE a.isApproved = 'Y' AND a.isHired = 'N'

SELECT a.id, a.photo, a.name, a.jobType, a.age, a.expSalary, a.religion, a.prefCity FROM candidates a LEFT JOIN candidate_subscribed_packages b ON b.candId = a.id AND b.isExpired = 'N' WHERE a.isApproved = 'Y' AND a.isHired = 'N' ORDER BY CASE WHEN b.createdAt IS NULL THEN 1 ELSE 0 END, b.createdAt DESC limit 9 offset 243