Compare two table and remove common row comparing with two column

Question

I have two table Table A & Table B.

Table A have userid and course_id and Table B have userid & courseid.

I want one table without all the records which have in table B. It should not contain any records which are in Table B.

For eg.

If table A have:

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

If table B have:

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

Then In new table should have:

userid: 224 courseid: 8

My code for table 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

Table 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 )

OUTPUT:

TABLE A OUTPUT

TABLE B OUTPUT

I don't want any row from table B, which have in table A comparing with userid and courseid.

I tried this query to get my desired output. But it excluded all user (userid)from table A which is common in table B.

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))

Thank you.

Marcello Perri · Accepted Answer · 2019-02-08 11:50:34Z

0

In other words, you are looking for a table that contains the rows of table A where table A userid equal table B userid and table A courseid different table B courseid, so your query will be:

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

This should work

edited Feb 8, 2019 at 11:50

answered Feb 8, 2019 at 11:37

Marcello Perri

6181 gold badge9 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Robert Over a year ago

In this case, I will get userid which is equal in both table, then I will lose other user. Which I don't want. Just I want to remove all records which are in table B comparing with two columns.

Marcello Perri Over a year ago

I just edited my answer with 'SELECT a.userid, a.courseid FROM a, b WHERE (a.userid = b.userid AND a.courseid != b.courseid) OR a.userid != b.userid' this should work I think

Tik · Accepted Answer · 2019-02-08 11:51:47Z

0

You should be able to do a join like this.

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

This diagram always helps

answered Feb 8, 2019 at 11:51

Tik

8826 silver badges14 bronze badges

Collectives™ on Stack Overflow

Compare two table and remove common row comparing with two column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related