0

Hi Guys i have 2 tables (user/logs kinda thing)

tk_assets (the users) tk_sessions (the logs) <-- where each log contains both stamp_in and stamp_out epoch values (making up 1 session of the user.

User is considered logged in when its latest session does not have a stamp_out value (still logged in)

I simply need to retrieve all users in the database along its "LATEST SESSION"..

In plain english, this would be "SELECT each user as well as its most recent session"

I can't seem to figure out the proper SQL statement for this.. 

SELECT
tk_assets.ass_id,
tk_sessions.stamp_in,
tk_sessions.stamp_out
FROM
tk_assets
LEFT JOIN tk_sessions ON tk_assets.ass_id = tk_sessions.ass_id
GROUP BY
tk_sessions.ass_id
ORDER BY
tk_sessions.stamp_in DESC

this is as far as i got..

BrownChiLD
  • 3,545
  • 9
  • 43
  • 61

2 Answers2

0

Unless you need other information, MAX should help you out.

SELECT u.*, MAX(l.stamp_out) FROM user u
INNER JOIN logs l ON l.user_id = u.id
WHERE l.stamp_out = 0
GROUP BY u.id

If you need more that that, take a look at this question: MySQL JOIN the most recent row only?

Community
  • 1
  • 1
Grim...
  • 16,518
  • 7
  • 45
  • 61
0

Yoh your naming conventions are a bit rough

SELECT assets.`ass_id`, sessions.`stamp_in`, sessions.`stamp_out`, sessions.`ass_id`
FROM tk_assets assets
LEFT JOIN tk_sessions sessions
ON assets.`ass_id` = sessions.`ass_id`
WHERE assets.`ass_id` = sessions.`ass_id`
GROUP BY sessions.`ass_id`
ORDER BY sessions.`stamp_in` DESC
kay
  • 337
  • 3
  • 7