Jump to content

MySQL JOIN Issue


axiom82

Recommended Posts

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 :)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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;

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.