MYSQL：一列上有多个“矛盾”的条件

Question

I'm trying to create a query that ONLY outputs members if they meet 2 conditions.

The first condition: If the user has a matching "courseID".

The second condition: If the user doesn't have a matching "courseID".

Obviously this is contradicting so let me explain.

I have two tables...

members

• ID
• email

certifications

• ID
• memberID
• courseID

When members complete a course they gain a certification. The certification stores their memberID and the courseID. Members can have multiple certifications.

The end goal is to create an email sending list to promote courses.

Let's say we have two courses. "Beginner"(courseID=1) and "Intermediate"(courseID=2).

One member could have both certifications, one for each course. Another member could just have the one, the "Beginner" certification.

I want to send a promotional email to all members who have the "Beginner" certification. However I want to exclude any members with the "Intermediate" certification.

The problem I can't seem to overcome is how to exclude the members with the "Intermediate" certification.

**I have simplified this example, in truth there is a vast amount of different certification types **

Below is a rough query I've been trying... I've tried so, so many different queries.

Any help would be great, thank you.

// Look Up Recipients
$recipientsQuery = $wpdb->get_results("
SELECT DISTINCT(email) 
FROM $memberTable
LEFT JOIN $certificationsTable
ON $memberTable.ID = $certificationsTable.memberID
WHERE courseID = 4 AND courseID != 5
");

Answer 1

SELECT m.ID, m.email
   , MAX(IF(c.courseID = 4, 1, 0)) AS hasCourse4
   , MAX(IF(c.courseID = 5, 1, 0)) AS hasCourse5
FROM members AS m 
LEFT JOIN certifications AS c ON m.ID = c.memberID
HAVING hasCourse4 = 1 AND hasCourse5 = 0
;

Alternatively, you could join certs to members to certs, this might be faster:

SELECT m.email
FROM certifications AS c1
INNER JOIN members AS m ON c1.memberID = m.ID
LEFT JOIN certifications AS c2 ON m.ID = c2.memberID AND c2.courseID = 5
WHERE c1.courseID = 4 AND c2.courseID IS NULL
;