mysql - 比较两个表并删除与两列比较的公共(public)行

Question

我有两个表 Table A 和 Table B。

A 表有 userid 和 course_id，B 表有 userid 和 courseid。

我想要一个没有表 B 中所有记录的表。它不应该包含表 B 中的任何记录。

例如。

如果表A有:

userid: 224 courseid: 6
userid: 224 courseid: 7
userid: 224 courseid: 8

如果表B有:

userid: 224 courseid: 6
userid: 224 courseid: 7

然后在新表中应该有:

userid: 224 courseid: 8

我的表 A 代码:

SELECT ue.userid                            AS userid, 
       Concat(u.firstname, ' ', u.lastname) AS user, 
       en.courseid                          AS course_Id, 
       co.fullname                          AS Course_Name, 
       cu.companyid                         AS companyId, 
       company.name 
FROM   `mdl_user_enrolments` AS ue 
       LEFT JOIN `mdl_enrol` AS en 
              ON ue.enrolid = en.id 
       LEFT JOIN `mdl_course` AS co 
              ON en.courseid = co.id 
       LEFT JOIN `mdl_user` AS u 
              ON ue.userid = u.id 
       LEFT JOIN `mdl_company_users` AS cu 
              ON cu.userid = ue.userid 
       LEFT JOIN `mdl_company` AS company 
              ON company.id = cu.companyid 
WHERE  co.id IN ( 1, 2, 3, 4, 
                  5, 6, 7, 8, 
                  9, 10, 11, 12 ) 
       AND company.id = 1 

表 B:

SELECT u.id, 
       p.course 
FROM   mdl_course_completions AS p 
       JOIN mdl_course AS c 
         ON p.course = c.id 
       JOIN mdl_user AS u 
         ON p.userid = u.id 
WHERE  c.enablecompletion = 1 
       AND p.course IN ( 1, 2, 3, 4, 
                         5, 6, 7, 8, 
                         9, 10, 11, 12 ) 

输出:

TABLE A OUTPUT

TABLE B OUTPUT

我不想要表 B 中的任何行，这些行在表 A 中与 userid 和 courseid 进行比较。

我尝试了这个查询以获得我想要的输出。但是它排除了表A中常见的所有用户(userid)。

SELECT ue.userid as userid,CONCAT(u.firstname,' ',u.lastname) as user,en.courseid as course_Id ,co.fullname as Course_Name,cu.companyid as companyId ,company.name FROM mdl_user_enrolments as ue left join mdl_enrol as en ON ue.enrolid = en.id left join mdl_course as co ON en.courseid = co.id left join mdl_user as u ON ue.userid = u.id left join mdl_company_users as cu ON cu.userid = ue.userid left join mdl_company as company ON company.id = cu.companyid where co.id IN (1,2,3,4,5,6,7,8,9,10,11,12) AND company.id = 1 AND ue.userid NOT IN (SELECT u.id FROM mdl_course_completions AS p JOIN mdl_course AS c ON p.course = c.id JOIN mdl_user AS u ON p.userid = u.id WHERE c.enablecompletion = 1 AND p.course IN (1,2,3,4,5,6,7,8,9,10,11,12))

谢谢。

Answer 1

换句话说，您要查找的表包含表 A 的行，其中表 A userid 等于表 B userid，表 A courseid 与表 B courseid 不同，因此您的查询将是:

SELECT a.userid, a.courseid FROM a, b WHERE (a.userid = b.userid AND a.courseid != b.courseid) OR a.userid != b.userid

这应该可行