exatma exatma - 3 months ago 7
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

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.