I'm creating an application that helps keep track of weight / time entries in the gym! I’ve created an overview screen, which should contain a list of machines which are filtered by an array of persons UUID’s and a day number. I was able to construct this query using an INNER JOIN and IN operator.
The problem is that I also want to fetch the latest entry for every person / machine combo, below I’ve included a simplified dataset for explanation:
Person table
| uuid | name |
|---|---|
| 12297EBD96B54C6C95935C2001634B77 | Wouter de Bruijn |
| 59F2EA7AA33E4EFE9F2484E7B37C46C1 | Sjors van Holst |
Schedule table
| uuid | machine | person | day |
|---|---|---|---|
| 1D02BF7E755F48968B9701C5D287DE7A | 70104A0A5DD04C2AB7F84D7F8051A8BA | 12297EBD96B54C6C95935C2001634B77 | 2 |
| 3044B63EFFD24572863014984BBF299F | 70104A0A5DD04C2AB7F84D7F8051A8BA | 59F2EA7AA33E4EFE9F2484E7B37C46C1 | 2 |
Entry table
| uuid | machine | person | weight | created |
|---|---|---|---|---|
| 324604B034844B9D8ED4BED03F5193E9 | 70104A0A5DD04C2AB7F84D7F8051A8BA | 12297EBD96B54C6C95935C2001634B77 | 50 | 2022-04-08 12:00:00 |
| 336B370D2CBE43A1B4ED420143A22C32 | 70104A0A5DD04C2AB7F84D7F8051A8BA | 12297EBD96B54C6C95935C2001634B77 | 5 | 2022-04-07 12:00:00 |
Machine
| machine | name |
|---|---|
| 70104A0A5DD04C2AB7F84D7F8051A8BA | Bench press |
I'm trying to construct a query that would take in a list of UUID's (let's say 12297EBD96B54C6C95935C2001634B77 and 59F2EA7AA33E4EFE9F2484E7B37C46C1) and a day (let's say 2) and then return something along the lines of:
Results
| machine | person | weight |
|---|---|---|
| 70104A0A5DD04C2AB7F84D7F8051A8BA | 12297EBD96B54C6C95935C2001634B77 | null |
| 70104A0A5DD04C2AB7F84D7F8051A8BA | 59F2EA7AA33E4EFE9F2484E7B37C46C1 | 50 |
But I can't figure out how to construct a query for this, and would appreciate any help with this! Below I’ve also included my ERD in case it might come in handy, but a query using my sample dataset would also be fine. I’m pretty sure I could change the query to match the actual database.
I would include what I’ve attempted so far but I don’t think this would help anyone out. I'm not new to MySQL or anything but I’ve never had to write such a complex query. So I was hoping for some help :)
