Q:

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)

A:

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.

