I have the following tables:
Club: Club_ID | Title | Created_Date | ...
Club_Intesect: User_ID | Club_ID | Access
I'm trying to select a variable number of clubs, and join the ID of the user with the highest access in that club. This person is considered the owner.
So if Club 100 has Members A, B, C with access 3,4, and 5 respectively: I want the final query to select from club:
Club.Club_ID Club.Title Club.Created_Date Club_Intersect.User_ID
100 | "Test Club" | "Creation Date" | C |
101 | "Test Club 2" | "Creation_Date" | Highest Access User |
...