比较两个表，并与两个列比较删除公共行

Question

我有两张表A和表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 ) 

产出：

表A产出

表B产出

我不想要表B中的任何行，它在表A中与userid和courseid相比。

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

选择ue.userid作为用户as，CONCAT(u.firstname，‘'，u.lastname)作为用户，en.courseid作为course_Id，co.fullname作为Course_Name，cu.companyid作为companyId，company.name从mdl_user_enrolments ON ue左加入mdl_enrol as en ON ue.enrolid = en.id左连接mdl_course as co en.courseid = co.id左连接mdl_user IN ue.userid = u.id左加入mdl_company_users as cu.userid = ue.userid左加入mdl_company as company.id = cu.companyid IN (1,2,3,4,5,6,7,8，9,10,11,12)和company.id =1和ue.userid NOT IN (从mdl_course_completions中选择u.id作为p JOIN mdl_course AS c ON p.course = c.id JOIN mdl_user AS u ON p.userid = u.id，其中c.enablecompletion =1和p.course IN (1,2,3,4,5,6,7,8,9,10,11,12))

谢谢。

Answer 1

换句话说，您正在寻找一个包含表A的行的表，其中表A的userid等于表B的userid，而表A的内容与表B的内容不同，所以您的查询将是：

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

这应该能行

Answer 2

你应该能做这样的连接。

SELECT  a.userid, a.courseid
FROM TABLE a
LEFT JOIN TABLE b
ON a.userid = b.userid and a.courseid = b.courseid)x
WHERE b.userid IS NULL

这个图表总是有帮助的。

​