Convoluted MySql Query

[showman13]

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.

[rubberman]

Show your work. Just stating the problem would give us enough information.

[showman13]

I can show you what I know doesn't work...

SELECT q.mem_id, q.user
FROM members as q, members as p
WHERE q.ref_id = $referrer_id
AND p.ref_id = $referrer_id
AND count(p.qualify_id) < 9
AND count(p.qualify_id) = min(p.qualify_id)
GROUP BY p.qualify_id

[pritaeas]

SELECT q.mem_id, q.user
FROM members as q, members as p
WHERE q.ref_id = $referrer_id
AND p.ref_id = $referrer_id
GROUP BY p.qualify_id
HAVING count(p.qualify_id) < 9
AND count(p.qualify_id) = min(p.qualify_id)

[showman13]

Thank you for that lesson in query structure pritaeas. I've always sucked at that.
Unfortunately, the results remain the same

Maybe I'm just either going about this all wrong or asking the wrong question
let me see if I can define what I'm trying to accomplish a little better

I am working with a small amount of data in the table because I need this to work for every referral beginning with the first.
This is what the relavant fields in the members table looks like

$members = array(
  array('mem_id' => '2','user' => 'Zero','create_date' => '2016-04-25 01:44:18','ref_id' => '0','qualify_id' => '0'),
  array('mem_id' => '3','user' => 'First','create_date' => '2016-04-28 08:57:23','ref_id' => '2','qualify_id' => '2'),
  array('mem_id' => '4','user' => 'Second','create_date' => '2016-05-03 17:39:48','ref_id' => '2','qualify_id' => '2'),
  array('mem_id' => '5','user' => 'Third','create_date' => '2016-05-04 17:41:53','ref_id' => '3','qualify_id' => '3'),
  array('mem_id' => '6','user' => 'Fourth','create_date' => '2016-05-05 13:33:55','ref_id' => '3','qualify_id' => '3'),
  array('mem_id' => '8','user' => 'Fifth','create_date' => '2016-05-07 13:26:22','ref_id' => '4','qualify_id' => '3'),
  array('mem_id' => '9','user' => 'Sixth','create_date' => '2016-05-30 09:21:33','ref_id' => '2','qualify_id' => '3')
);

From that data I want to extract the mem_id and user of the member that
1) has a ref_id = 2 - there are 3 members that match #3 / #4 / #9
and
2) whose mem_id has the lowest number of matches to qualify_id in the same table
mem_id 3 matches qualify_id in 4 different records
mem_id 4 and mem_id 9 have ZERO matches in qualify_id
so it would be either 4 or 9
and
3) has the oldest create_date
mem_id 4 has a create_date < mem_id 9 create_date

So, what I need to be returned from the query is the mem_id 4 with user 'Second'

I hope all that makes sense...

Can that be accomplished with a single query?

Or will I need to create a function with multiple queries to drill down to that single mem_id that I am looking for?

This is as close as I've gotten so far but this query only returns a record that actually has matching values in the qualify_id, so mem_id 4 and 9 are not considered.
And what I get back is mem_id '3' user 'First'

SELECT q.mem_id, q.user, q.ref_id, count(p.mem_id) as qualifiers
FROM members as q, members as p
WHERE q.ref_id = 2
AND p.qualify_id = q.mem_id
GROUP BY p.qualify_id
ORDER BY q.create_date
LIMIT 1

[showman13]

Well, gotta say it is rather disappointing to not get any sort of response from anyone in here.

BUT, in case anyone is interested, I did finally piece together a query after countless hours of trial and error.

it appears to work just fine for what I wanted it to do.
It produces the mem_id of the member with the lowest number of qualifier_ids assigned to them, and in the case of a tie, then it takes the one that joined first (lowest mem_id number)

But if anyone has the time and the inclination to look at it and tell me if there are any glaring errors in how I have it structured, I would certainly appreciate that feedback.

thanks in advance
Douglas

SELECT q.mem_id, q.user, q.ref_id, q.mem_status, COUNT( p.mem_id ) AS qualifiers
FROM members AS q
LEFT JOIN members AS p ON q.mem_id = p.qualify_id
GROUP BY q.mem_id
HAVING q.ref_id = '".$referer_id."'
AND q.mem_status =  'C'
AND COUNT( p.mem_id ) >=0
AND COUNT( p.mem_id ) <9
ORDER BY COUNT( p.mem_id ) ASC , q.mem_id ASC
LIMIT 1