Select values from another table with most recent entry

Question

I have 2 MySQL Tables:

Table with all Events

+----+---------------------+
| id | Eventtitle          |
+----+---------------------+
|  1 | Event 1             |
|  2 | Event 2             |
|  3 | Event 3             |
|  4 | Event 4             |
+----+---------------------+

Table with user attend statuses - Users can change their status multiple times. Every time they change it, a new entry is inserted into the table

+----+------------+----------+---------+---------------------+
| id | event_id   | user_id  | status  | attend_time         |
+----+------------+----------+---------+---------------------+
|  1 | 1          | 2        | 1       |2013-07-03 15:34:02  |
|  2 | 1          | 2        | 2       |2013-08-03 19:01:02  |  <--
|  3 | 3          | 1        | 1       |2013-07-03 15:34:02  |
|  4 | 4          | 4        | 3       |2013-07-03 15:34:02  |
|  5 | 4          | 6        | 2       |2013-07-03 15:34:02  |
|  6 | 4          | 6        | 1       |2013-07-03 18:55:02  |  <--
+----+-----------------------+---------+---------------------+

Now I want all events listed and additionally the most recent attend state and attend_time of the currently logged in user (for example user_id 2 oder 54) - IF he is not in the attend table i still need an entry of the event. (in that case a NULL for the state and attend_time would be good)

Here I rebuild the Data Structure: http://sqlfiddle.com/#!2/c1b3f

IF there is no way on how to get the result with MySQL - I will try to get it with PHP

can you please explain how the result list you are wanting should look like? it should contain the most recent state of the current user(2), i know - but what else? — low_rents, May 30 '14 at 16:32
possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — Marcus Adams, May 30 '14 at 16:34
@northkildonan: It should contain: event_id | event_title | status | attend_time 1 | Event 1 | 2 | 2013-08-03 19:01:02 First 2 are from events table and last 2 are from status table. But as you can see, it should NOT be the status with ID 1 but the status with ID 2 — Chalkin, May 30 '14 at 17:49

DarkAjax · Answer 1 · 2014-05-30T16:50:54.583

0

Try something like this:

SELECT s.id, eventtitle, status, attend_time
FROM events e
JOIN status s ON e.id = s.event_id
WHERE user_id = 2 AND attend_time = (SELECT MAX(attend_time) FROM status
                                       WHERE user_id = 2)

SQLFiddle

Or:

SELECT s.id, eventtitle, s.status, s.attend_time
FROM events e
JOIN status s ON e.id = s.event_id
LEFT JOIN status s2 ON s.attend_time < s2.attend_time
WHERE s.user_id = 2 AND  s2.attend_time IS NULL

SQLFiddle

edited May 30 '14 at 16:50

answered May 30 '14 at 16:36

DarkAjax

15,955
11
53
65

I have tried to use your solutions here: http://sqlfiddle.com/#!2/c1b3f/2 but they seem not to work since it's not listing results if the filtered user has no entry in the second table – Chalkin May 30 '14 at 17:56

score 0 · Answer 2 · answered May 30 '14 at 17:09

0

(not tested)

SELECT a.id, eventtitle, status, attend_time FROM events b JOIN status a ON b.id = a.event_id WHERE user_id = 2 order by attend_time desc

answered May 30 '14 at 17:09

ɹɐqʞɐ zoɹǝɟ

4,342
3
22
35

Select values from another table with most recent entry

2 Answers2