There is a query that returns a value from 2 tables that meet a certain condition; you need to do so that the query also returns the number of records that meet the condition, the query itself:

SELECT c.id, c.first_name, c.last_name, c.speciality, c.level, c.email, c.skype, c.city, s.status_type, c.status_id, c.linkedin, c.link_cv, c.interview_res, c.createdAt, c.updatedAt, c.recruiter_id, u.first_name AS fname, u.last_name AS lname FROM Candidates c JOIN Users u ON c.recruiter_id = u.id JOIN Statuses s ON s.id = c.status_id WHERE c.deleted = false

    2 answers 2

    Since MySQL does not support window functions and common table expressions, I see no other way than repeating the entire query:

     SELECT c.id, c.first_name, c.last_name, c.speciality, c.level, c.email, c.skype, c.city, s.status_type, c.status_id, c.linkedin, c.link_cv, c.interview_res, c.createdAt, c.updatedAt, c.recruiter_id, u.first_name AS fname, u.last_name AS lname ,(SELECT count(*) FROM Candidates c JOIN Users u ON c.recruiter_id = u.id JOIN Statuses s ON s.id = c.status_id WHERE c.deleted = false) cnt FROM Candidates c JOIN Users u ON c.recruiter_id = u.id JOIN Statuses s ON s.id = c.status_id WHERE c.deleted = false

    I don't know MySQL well.

    • Thanks, just what I was looking for. - Ihor Sh
    • By the way, window functions and CTE are already supported in MySQL. - msi

    Not sure if I understood the question correctly, but maybe like this:

     SELECT c.id, c.first_name, c.last_name, c.speciality, c.level, c.email, c.skype, c.city, s.status_type, c.status_id, c.linkedin, c.link_cv, c.interview_res, c.createdAt, c.updatedAt, c.recruiter_id, u.first_name AS fname, u.last_name AS lname, count(*) FROM Candidates c JOIN Users u ON c.recruiter_id = u.id JOIN Statuses s ON s.id = c.status_id WHERE c.deleted = false GROUP by c.id, c.first_name, c.last_name, c.speciality, c.level, c.email, c.skype, c.city, s.status_type, c.status_id, c.linkedin, c.link_cv, c.interview_res, c.createdAt, c.updatedAt, c.recruiter_id, u.first_name, u.last_name