CODEWITHSUNDEEP
mysqlmoodle
Robert
Robert

Reputation: 21

Compare two table and remove common row comparing with two column

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.

Upvotes: 2

Views: 53

Answers (2)

Tik
Tik

Reputation: 882

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 enter image description here

Upvotes: 0

Marcello Perri
Marcello Perri

Reputation: 610

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

Upvotes: 0

Related Questions