harrY harrY - 10 days ago 9
MySQL Question

mysql: how do i query max(dates) without losing corresponding values in Joins?

I got stuck with a Problem with a query:

roster
===========================
id | date | time
---------------------------
01 | 2016-09-01 | 20:00
02 | 2016-10-01 | 17:00
03 | 2016-11-01 | 18:00

roster_users
============================
id | rosterid | userid
----------------------------
01 | 01 | 01
02 | 02 | 01
03 | 01 | 02
04 | 03 | 03

user
===============
id | name
---------------
01 | Andreas
02 | Michael
03 | Anna
04 | Lena


So, my query should ideally return:

results
==============================================
name | RosterCount | LastDuty
----------------------------------------------
Andreas | 2 | 2016-10-01 | 17:00
Michael | 1 | 2016-09-01 | 20:00
Anna | 1 | 2016-11-01 | 18:00
Lena | 0 | Null


Here is what i have tried and mostly work:

SELECT
r.id,
u.name,
( SELECT COUNT(*)
FROM roster_users p
INNER JOIN roster l
ON l.id = p.rosterid AND l.valid = 1 AND l.date < CURDATE()
WHERE p.userid = ru.userid ) AS RosterCount
CONCAT_WS(' - ', max(r.date), r.time) AS LastDuty
FROM roster_users ru
INNER JOIN roster r
ON ru.rosterid = r.id
AND r.valid = 1
AND r.date < CURDATE()
RIGHT OUTER JOIN user u
ON u.id = ru.userid
GROUP BY u.id


My Problem is, that i get all informations i need, but the given r.time doesnt correspond with the r.date. (r.id of r.time is diffrent than the r.id of r.date)

hope you can help me out :-)

Answer

To select the max date and corresponding time:

  • Join date and time
  • Select the max datetime
  • Break datetime again

Moreover I've simplified your query, you made it too complex for no reason:

SELECT
    u.name,
    count(*) AS RosterCount,
    MAX(TIMESTAMP(r.date, r.time)) as MaxDateTime,
    CONCAT_WS(' | ', DATE_FORMAT(MaxDateTime, '%Y-%m-%d'), DATE_FORMAT(MaxDateTime,'%H:%i')) AS LastDuty
FROM
    user u LEFT JOIN roster_users ru
        ON u.id = ru.userid
    JOIN roster r
        ON r.id = ru.rosterid
WHERE
    r.valid = 1 AND r.date < CURDATE()
GROUP BY 
    u.id
ORDER BY 
    RosterCount DESC
Comments