Background:
In a table, each row represents a potential partnership between two users. Each user would typically have multiple potential partnership. Example - we’ll have user a,b,c,d,e. a could meet b,c,d,e, b could meet a,c,d,e and so on.
Each partnership would have two rows (one reflecting each user their partner along with user specific fields, that’s why two rows)
Here’s an example: fields:
PartnershipID, user, partner, time, expertise, status
Data (accordingly)
123, a, c, 05/01/2022 9:00 am, blockchain, confirmed
123, c, a, 05/01/2022 9:00 am, javascript, confirmed
What I’m trying to do:
Select only one partnership (the earliest one), but under the condition that none of the users have a confirmed meeting with anyone else (in other words, the meeting’s status is 'confirmed’Unconfirmed meetings would have a ‘maybe’ status.
What I’ve tried so far:
Going through the list of users (because I have less users than potential partnerships) and assigning a confirmed status to the first meeting found where each user is the user.
I’ve tried different ways to only assign a confirmed status where the number of conversations (count) where the user is either the partner or the user is 0, but this did not do the trick.
Also (if we’re future proofing it, but it’s not a must, at some point I would want to use a separate parameter (0,1,2,3) to decide how many partnership assign “confirmed” to. Right now it’s just one.
If anyone has an idea or a direction, I’d love to hear it.