php - MYSQL: Multiple "contradicting" conditions on one column -

i'm trying create query outputs members if meet 2 conditions.

the first condition: if user has matching "courseid".

the second condition: if user doesn't have matching "courseid".

obviously contradicting let me explain.

i have 2 tables...

members

• id
• email

certifications

• id
• memberid
• courseid

when members complete course gain certification. certification stores memberid , courseid. members can have multiple certifications.

the end goal create email sending list promote courses.

let's have 2 courses. "beginner"(courseid=1) , "intermediate"(courseid=2).

one member have both certifications, 1 each course. member have one, "beginner" certification.

i want send promotional email members have "beginner" certification. want exclude members "intermediate" certification.

the problem can't seem overcome how exclude members "intermediate" certification.

**i have simplified example, in truth there vast amount of different certification types **

below rough query i've been trying... i've tried so, many different queries.

any great, thank you.

// recipients $recipientsquery = $wpdb->get_results(" select distinct(email)  $membertable left join $certificationstable on $membertable.id = $certificationstable.memberid courseid = 4 , courseid != 5 "); 

select m.id, m.email    , max(if(c.courseid = 4, 1, 0)) hascourse4    , max(if(c.courseid = 5, 1, 0)) hascourse5 members m  left join certifications c on m.id = c.memberid having hascourse4 = 1 , hascourse5 = 0 ; 

alternatively, join certs members certs, might faster:

select m.email certifications c1 inner join members m on c1.memberid = m.id left join certifications c2 on m.id = c2.memberid , c2.courseid = 5 c1.courseid = 4 , c2.courseid null ;