All geek questions in one place

Does MySQL result result from multiple tables get the wrong result?

I have three tables: attendance, cv_target and candidate. I need to find the candidate counter for a specific user.

I am not an expert in MySQL. I tried to execute the query below, but I can not find the exact value.

SELECT
   attendance_date,
   cv_target_date_for,
   cv_requirement,
   job_id,
   cv_target,
   achi,
   recruiter_comment,
   recruiter_rating
FROM
   attendance f
RIGHT JOIN
   (
      SELECT
         cv_requirement,
         cv_target,
         cv_target_date_for,
         achi,
         recruiter_comment,
         recruiter_rating
      FROM
         cv_target a
      LEFT JOIN
         (
            SELECT
               COUNT(candidate_id) AS achi,
               cv_target_date,
               fk_job_id
            FROM
               candidate
            GROUP BY
               fk_job_id,
               cv_target_date
         ) b
            ON a.cv_requirement = b.fk_job_id
            AND a.cv_target_date_for = b.cv_target_date
      WHERE
         cv_target_date_for BETWEEN '2014-02-01' AND '2014-03-01'
         AND cv_recruiter = '36'
   ) c
      ON f.attendance_date=c.cv_target_date_for
GROUP BY
   cv_requirement,
   cv_target_date_for
ORDER BY
   c`.`cv_target_date_for` ASC

attendance

id   fk_user_id    attendance_date 
1    44            2014-02-24
2    44            2014-02-25
3    44            2014-02-26
4    44            2014-02-27
5    36            2014-02-24
6    44            2014-02-28

cv_target

id    cv_recruiter    cv_requirement    cv_target    cv_target_date_for
1     44              1                 3            2014-02-24
2     44              2                 2            2014-02-24
3     44              3                 2            2014-02-25
4     44              4                 3            2014-02-25
4     44              4                 3            2014-02-26

candidate

candidate_id    fk_posted_user_id    fk_job_id    cv_target_date
1               44                   1            2014-02-24
2               44                   3            2014-02-25
3               44                   3            2014-02-25
3               44                   4            2014-02-25
4               44                   4            2014-02-26
5               44                   5            2014-02-28
5               44                   5            2014-02-28

Desired Result

attendance_date    cv_target_date_for    job_id    cv_target    achi(count)
2014-02-24         2014-02-24            1         3            1
2014-02-24         2014-02-24            2         2            null
2014-02-25         2014-02-25            3         2            2
2014-02-25         2014-02-25            4         3            1
2014-02-26         2014-02-26            4         3            1
2014-02-27         2014-02-27            null      null         null
2014-02-28         null                  5         null         2

The result that I get

attendance_date    cv_target_date_for    job_id    cv_target    achi(count)
2014-02-24         2014-02-24            1         3            1
2014-02-24         2014-02-24            2         2            null
2014-02-25         2014-02-25            3         2            2
2014-02-25         2014-02-25            4         3            1
2014-02-26         2014-02-26            4         3            1

Date 27 and 28 are not displayed. I also need these values.

+4
source share
1 answer

Original answer

, , . , , . cv_target, . .

, . , . GROUP BY , . .

LEFT JOIN, . , ( WHERE) cv_target ( , ), .

:

SELECT
   DATE_FORMAT(a.attendance_date, '%Y-%m-%d') AS attendance_date,
   DATE_FORMAT(t.cv_target_date_for, '%Y-%m-%d') AS cv_target_date_for,
   t.cv_requirement AS job_id,
   t.cv_target,
   c.achi AS `achi(count)`
FROM
   attendance AS a
LEFT JOIN
   cv_target AS t
      ON a.fk_user_id = t.cv_recruiter
      AND a.attendance_date = t.cv_target_date_for
LEFT JOIN
   (
      SELECT
         COUNT(candidate_id) AS achi,
         fk_job_id,
         cv_target_date
      FROM
         candidate
      WHERE
         fk_posted_user_id = 44
         AND cv_target_date BETWEEN '2014-02-01' AND '2014-03-01'
      GROUP BY
         fk_job_id,
         cv_target_date
   ) AS c
      ON t.cv_requirement = c.fk_job_id
      AND t.cv_target_date_for = c.cv_target_date
WHERE
   a.fk_user_id = 44
   AND a.attendance_date BETWEEN '2014-02-01' AND '2014-03-01'
ORDER BY
   ISNULL(t.cv_target_date_for), t.cv_target_date_for, t.cv_requirement

, . , , .

AND cv_target_date BETWEEN '2014-02-01' AND '2014-03-01'

ISNULL NULL .

SQL Fiddle, , cv_target_date_for. , .

UPDATE

, cv_target , , . , .

, . , , , . , , .

, UNION cv_target . .

:

SELECT
   DATE_FORMAT(a.attendance_date, '%Y-%m-%d') AS attendance_date,
   DATE_FORMAT(t.cv_target_date_for, '%Y-%m-%d') AS cv_target_date_for,
   j.job_id,
   t.cv_target,
   c.achi AS `achi(count)`
FROM
   attendance AS a
LEFT JOIN
   (
      SELECT
         cv_requirement AS job_id,
         cv_target_date_for AS job_date
      FROM
         cv_target
      WHERE 
         cv_recruiter = 44
         AND cv_target_date_for BETWEEN '2014-02-01' AND '2014-03-01'
      UNION
      SELECT
         fk_job_id AS job_id,
         cv_target_date AS job_date
      FROM
         candidate
      WHERE
         fk_posted_user_id = 44
         AND cv_target_date BETWEEN '2014-02-01' AND '2014-03-01'
   ) AS j
      ON a.attendance_date = j.job_date
LEFT JOIN
   cv_target AS t
      ON a.fk_user_id = t.cv_recruiter
      AND j.job_id = t.cv_requirement
      AND j.job_date = t.cv_target_date_for
LEFT JOIN
   (
      SELECT
         COUNT(candidate_id) AS achi,
         fk_job_id,
         cv_target_date
      FROM
         candidate
      WHERE
         fk_posted_user_id = 44
         AND cv_target_date BETWEEN '2014-02-01' AND '2014-03-01'
      GROUP BY
         fk_job_id,
         cv_target_date
   ) AS c
      ON j.job_id = c.fk_job_id
      AND j.job_date = c.cv_target_date
WHERE
   a.fk_user_id = 44
   AND a.attendance_date BETWEEN '2014-02-01' AND '2014-03-01'
ORDER BY
   ISNULL(t.cv_target_date_for), t.cv_target_date_for, j.job_id

SQL Fiddle, , cv_target_date_for. , (.. 2014-02-27).

2014-02-28, cv_target. , , , cv_target_date_for.

cv_target , :

DATE_FORMAT(t.cv_target_date_for, '%Y-%m-%d') AS cv_target_date_for,

:

DATE_FORMAT(j.job_date, '%Y-%m-%d') AS job_date,

.

+1

Source: https://habr.com/ru/post/1529520/

More articles:


All Articles