I have a member table from which I'm trying to create a query using these fields
mem_id / username / create_date / mem_status / ref_id / qualify_id
A new member is mem_status 'F' with ref_id equal to the mem_id of their referrer and qualify_id of zero
When the member upgrades, the mem_status becomes 'C' and the qualify_id is changed to the mem_id of another member that is defined by options selected by their referrer.
In this case the referrer wants to distribute the qualify_id among their other personally referred members based on the number of qualify_ids they already have (selecting from the group with the lowest number), and then select the personal referral with the oldest 'create_date' on file
(plus, the number of qualify_ids must be less than 9)
So, effectively I need to
1) determine All mem_ids that have been referred by the referrer
2) determine the count of members that have a qualify_id that matches each of those mem_ids
3) Group together those with the lowest count that is < 9
4) select the mem_id from that group that has the oldest create_date on their member record
SIMPLE, RIGHT?? Not so much.
I've been trying for hours to figure out a query that would accomplish this and just return the individual mem_id, but have confused myself completely and ended up with a query that makes no sense at all.
Any assistance with this would be greatly appreciated.