I want to see the activity of a particular member in my hotel. I want to see which hotel rooms they visited. However, if they visited the same hotel rooms, I only want the most recent visit of that hotel room
customers
+--------+-------+-----------+
| cus_id | name | driver_id |
+--------+-------+-----------+
| 1 | bob | 11111 |
| 2 | james | 22222 |
| 3 | sam | 33333 |
| 4 | billy | 44444 |
+--------+-------+-----------+
hotel_rooms (cus_id is the owner of the room)
+----------+------------+--------+
| hroom_id | name | cus_id |
+----------+------------+--------+
| 1 | small room | 3 |
| 2 | big room | 1 |
+----------+------------+--------+
snapshots (when we detected the user in the room)
+-------------+----------+---------------------+
| snapshot_id | hroom_id | date_added |
+-------------+----------+---------------------+
| 1 | 1 | 2020-01-12 12:43:13 |
| 2 | 1 | 2020-01-13 17:23:53 |
| 3 | 2 | 2020-01-19 07:34:01 |
+-------------+----------+---------------------+
participants (who was also present in the same room at the particular time we detected the customer in the room)
+----------------+-------------+--------+
| participant_id | snapshot_id | cus_id |
+----------------+-------------+--------+
| 1 | 1 | 1 |
| 2 | 1 | 3 |
| 3 | 2 | 1 |
| 4 | 2 | 2 |
| 5 | 2 | 3 |
| 6 | 3 | 1 |
| 7 | 3 | 4 |
+----------------+-------------+--------+
Essentially what the participant table says is:
snapshot_id=1 bob and sam where in small room.snapshot_id=2, bob, james and sam where in small room.snapshot_id=3, bob and billy where in big room.The desired result for looking up bob's activity:
+-------------+------------+-----------------+---------------------+-------------------------+
| snapshot_id | name | owner_driver_id | date_added | participants_driver_ids |
+-------------+------------+-----------------+---------------------+-------------------------+
| 2 | small room | 33333 | 2020-01-13 17:23:53 | 11111,22222,33333 |
| 3 | big room | 11111 | 2020-01-19 07:34:01 | 11111,44444 |
+-------------+------------+-----------------+---------------------+-------------------------+
I cannot figure out how to combine the driver_id for both the owner and participants
SELECT s.snapshot_id, hr.name, c.driver_id as owner_driver_id, MAX(s.date_added) as date_added, GROUP_CONCAT(p.driver_id) as participants_driver_ids
FROM hotel_rooms hr INNER JOIN
snapshots s
ON hr.hroom_id = s.hroom_id JOIN
participants p
ON s.snapshot_id = p.snapshot_id JOIN
customers c
ON hr.cus_id = c.cus_id
WHERE p.cus_id = 1
GROUP BY hr.cus_id
ORDER BY date_added
You can get the result you want by first finding the maximum snapshot date for each room that bob was in, and then joining that to the snapshots table to get the latest snapshot for each room. Then you need to join to customers twice, once for the room owner's name, and once to get each of the participants names:
SELECT s.snapshot_id,
hr.name,
c1.driver_id AS owner_driver_id,
md.max_date AS date_added,
GROUP_CONCAT(c2.driver_id) AS participants_driver_ids
FROM snapshots s
JOIN (
SELECT hr.hroom_id, MAX(date_added) AS max_date
FROM hotel_rooms hr
JOIN snapshots s ON s.hroom_id = hr.hroom_id
JOIN participants p ON p.snapshot_id = s.snapshot_id
JOIN customers c ON c.cus_id = p.cus_id
WHERE c.name = 'bob'
GROUP BY hr.hroom_id, hr.name
) md ON md.hroom_id = s.hroom_id AND md.max_date = s.date_added
JOIN hotel_rooms hr ON hr.hroom_id = s.hroom_id
JOIN customers c1 ON c1.cus_id = hr.cus_id
JOIN participants p ON p.snapshot_id = s.snapshot_id
JOIN customers c2 ON c2.cus_id = p.cus_id
GROUP BY s.snapshot_id, hr.name, c1.driver_id, md.max_date
ORDER BY md.max_date DESC
LIMIT 15
Output:
snapshot_id name owner_driver_id date_added participants_driver_ids
2 small room 33333 2020-01-13 17:23:53 11111,33333,22222
3 big room 11111 2020-01-19 07:34:01 44444,11111