dongqiang4986
2015-07-07 23:15 阅读 38
已采纳

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

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
");
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • 已采纳
    duanpang5583 duanpang5583 2015-07-07 23:20
    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
    ;
    
    点赞 评论 复制链接分享
  • duannuci4008 duannuci4008 2015-07-07 23:24

    Try this.. Get what you need, and take away what you dont..

    SELECT *
    FROM $memberTable
    LEFT JOIN $certificationsTable USING (ID)
    WHERE courseID = 4
    AND ID NOT IN (
        SELECT ID
        FROM $memberTable
        LEFT JOIN $certificationsTable USING (ID)
        WHERE courseID = 5
    )
    
    点赞 评论 复制链接分享

相关推荐