exatma exatma - 1 year ago 67
MySQL Question

compare values of two table with several dates in one table

I have two table

employee

+=======+==========+
|id |level |
+=======+==========+
|1 |1 |
|2 |2 |
+=======+==========+


emp_level

+=======+========+==========+
|emp_id |level |date |
+=======+========+==========+
|1 |2 |2016-08-01|
|1 |1 |2012-08-01|
|2 |2 |2012-08-01|
+=======+========+==========+


I want to see if the level from
employee
matched with
emp_level
in recent date, so kinda like this

+=================+===============+===============+
|emp_level.emp_id |employee.level |emp_level.level|
+=================+===============+===============+
|1 |1 |2 |
|2 |2 |2 |
+=================+===============+===============+


I've tried
UNION
but the result from second table is on different row, and I don't know how to do it for all emp_id. my query:

(select id, level from employee where id="957")
union all
(select emp_id as id, level from emp_level where
emp_id= "957" order by date desc limit 1)

Answer Source

You can try the following query:

SELECT 
E.id,
E.level,
maxDateLevelTable.level
FROM employee E 
LEFT JOIN 
(
    SELECT 
    EL.*
    FROM emp_level EL
    INNER JOIN 
    (
        SELECT 
        emp_id,
        MAX(date) max_date
        FROM emp_level
        GROUP BY emp_id
    ) AS t
    ON t.emp_id = EL.emp_id AND t.max_date= EL.date
) AS maxDateLevelTable

ON E.id = maxDateLevelTable.emp_id
ORDER BY E.id;

See Demo

Note: You can replace the LEFT JOIN by INNER JOIN if there's always an entry found for each employee in emp_level table.

Explanation:

If you run this query alone

    SELECT 
     EL.*
    FROM emp_level EL
    INNER JOIN 
    (
        SELECT 
        emp_id,
        MAX(date) max_date
        FROM emp_level
        GROUP BY emp_id
    ) AS t
    ON t.emp_id = EL.emp_id AND t.max_date= EL.date

then you will get each employee's most recent data from emp_level table.

Later make an inner join between the result of this above query and employee table in order to get the corresponding level from employee table.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download