MySQL JOIN Issue

[axiom82]

I have attached a screenshot to this message to help describe my issue exactly.

 

I have two tables: employee and employee_timestamp.

I need to JOIN them together and GROUP BY the employee_id field which I have successfully done.

 

The next step is the one that is the problem.

When I do the GROUP BY, I want to retrieve the LATEST employee_timestamp record.

 

Here is the query (not working) and I've attached a screenshot to describe the issue.

 

SELECT e.id, et.date, max(et.date) AS max_date FROM employee e
LEFT JOIN (employee_timestamp et) ON (et.id = e.id)
GROUP BY e.id HAVING max_date;

 

You can see on the screenshot that the two date columns do not match...which means the latest timestamp was not retrieved as the "joined timestamp row".  It's important that I have that row as it has other important information in it.

 

Please help

[aschk]

So you want the latest timestamp for each employee?

 

SELECT e.id, x.max_date
FROM employee e
JOIN (
SELECT id, MAX(date) as 'max_date'
FROM employee_timestamp et
GROUP BY id
) x ON x.id = e.id;

 

Just taking a look over your info where you say

the latest timestamp was not retrieved as the "joined timestamp row"

I think that statement is wrong. Because you're not specified the date in your GROUP BY, MySQL can use a random row for the date column you have selected. So your method of "matching up" is incorrect I believe.

[axiom82]

Yes.  Thank you!  This works perfectly...now I can move forward

[axiom82]

That's getting closer but not entirely right.  The objective is to return not only the max_date FROM the employee_timestamp table, but to return the entire row where the max_date value exists.

 

This is more along the lines of what I want to accomplish...but it of course doesn't work as expected.

 

SELECT e.id, et.event, et.max_date
FROM employee e
JOIN (
SELECT employee_id, event, date
FROM employee_timestamp et
ORDER BY date DESC LIMIT 1
) x ON x.employee_id = e.id

[axiom82]

This is working perfectly...but damn it's a lot of code for an SQL query LOL...

 

SELECT e.id, et2.event, et2.date
FROM employee e
JOIN (
SELECT employee_id, max(date) as max_date
FROM employee_timestamp
GROUP BY employee_id
) et ON et.employee_id = e.id
JOIN (
SELECT employee_id, event, date
FROM employee_timestamp
) et2 ON et2.employee_id = e.id AND et2.date=et.max_date

ORDER BY e.id

[acidglitter]

i've never even heard of left join (theres left OuteR join..)... i think it would work if you used your original code, but changed left to inner..

 

SELECT e.id, et.date, max(et.date) AS max_date FROM employee e
INNER JOIN (employee_timestamp et) ON (et.id = e.id)
GROUP BY e.id HAVING max_date;

[aschk]

Ah i see what you're after now.

You want the event that corresponds to the max_date from the employee_timestamp table.

 

Try:

SELECT e.id, x.event, x.max_date
FROM employee e
JOIN (
SELECT id, event, MAX(date) as 'max_date'
FROM employee_timestamp et
GROUP BY id, event HAVING `date` = max_date
) x ON x.id = e.id;