MySQL Aggregate Query with multiple Conditions

[n1concepts 0]

Hi,

I need some help to define the correct SQL query that will accomplish the following:

VERSION: 10.0.38-MariaDB

• Select the 'member_id' based on the COUNT function using the 'receiver_id' column in the 'members' table
  • Conditions are:
    • WHERE 'astatus = 2' for 'member_id' (this condition 1st to filter before next condition actioned
    • COUNT(receiver_id) is LESS than 4 or IS NULL

The goal is to list the member_id that meets those two conditions.

Now, I have two queries defined which giving me the results I want but I'm trying to combined those two into one query AND add the WHERE condition for 'astatus = 2').

I need help with this part as I can't see the logic to combined the two queries with OR statement and also (for both) add the condition for the 'astatus = 2'.

Any help, surely, appreciated - thx!

Screen shot of table excerpt below along with queries

# Get Count of donors assigned to a receiver_id
SELECT m.receiver_id, COUNT(m.receiver_id) AS donor_count   
FROM members AS m 
WHERE (m.receiver_id IS NOT NULL) 
GROUP BY m.receiver_id 
HAVING COUNT(m.receiver_id) < 4 
LIMIT 1;


# Find member that's not yet assigned a receiver_id
SELECT m.member_id 
FROM members AS m 
WHERE (m.receiver_id IS NULL) 
Limit 4;

 

Edited April 2, 2019 by n1concepts

[kicken 541]

You red text in the screenshot confuses me, but it sounds like you want to find the first member_id that has less than entries in the receiver_id column but that doesn't really make any sense to me.

I think you'll need to explain what your desired end result and goal is better.

[n1concepts 0]

Hi Kicken,

my apologizes - i can see how that explanation is confusion so let me provide the answer (it came to me right after I posted for help...)

Answer: UNION - I just joined the two queries based on the UNION clause to get the results I needed (worked perfectly after some adjustments to each query).
Yes, you are correct - I was after the 'member_id' which is the primary_key in table which is also 'receiver_id' 2nd key.

thx & problem sovled!

[Barand 1,528]

For future reference, I would have helped but I had a problem loading that picture of your data into a test table.

Normally I would load posted data into a test table to work on a problem but queries wouldn't work with all the data in a blob field.