Mysql] Find the first and the last date of event a user joined with different roles

Question

I have three tables and I need to find the very first date and the very last(recent) date of an event users has joined as a host and a guest. In other words, the returned data should look like the following:

(please note that it's the very first and the very last(recent) date of all events a user has attended)

+----+-----+---------+----------------------+-----------------------+-----------------------+-----------------------+
| id |name |  Event  | First date as a host | Recent date as a host | First date as a guest | Recentdate as a guest |
+----+-----+---------+----------------------+-----------------------+-----------------------+-----------------------+
|  1 | Teo | Dance   | 2018-01-01           | 2017-01-01            | 2016-02-01            | 2017-09-09            |
|  2 | Jac | Karaoke | 2018-03-02           | 2018-01-01            | 2016-07-07            | 2017-01-06            |
|  3 | Lay | Boxing  | 2017-09-09           | 2018-07-07            | 2017-01-01            | 2018-02-02            |
+----+-----+---------+----------------------+-----------------------+-----------------------+-----------------------+

Tables

Users
id
name

Event
id
name
starts_at

Event_Users
id
user_id
event_id
role

I tried to follow an instruction here[https://www.periscopedata.com/blog/4-ways-to-join-only-the-first-row-in-sql]... but when it comes to writing a query that retrieves 4 different first rows(min/max), i always end up with a super messy query that doesn't even work...

What would be the easiest solution for this kind of tricky requirement?

Thanks

Post your "super messy query that doesn't work" in your question. There's likely no non-messy way to do this, so your attempt may not be far off base — WillardSolutions, Jun 06 '18 at 20:26

Barmar · Accepted Answer · 2018-06-06T22:44:46.090

This follows the same pattern as need to return two sets of data with two different where clauses except it uses MIN and MAX instead of SUM.

SELECT u.id, u.name, e.name AS Event,
    MIN(IF(eu.role = 'host', e.starts_at, NULL)) AS First_date_as_host,
    MAX(IF(eu.role = 'host', e.starts_at, NULL)) AS Recent_date_as_host,
    MIN(IF(eu.role = 'guest', e.starts_at, NULL)) AS First_date_as_guest,
    MAX(IF(eu.role = 'guest', e.starts_at, NULL)) AS Recent_date_as_guest
FROM users AS u
JOIN event_users AS eu ON eu.user_id = u.id
JOIN events AS e ON e.id = eu.event_id
GROUP BY u.id, e.id

Mysql] Find the first and the last date of event a user joined with different roles

1 Answers1