How to inner join for the resultted query in mysql

Question

I have an mysql query for getting which user assigned to which course and if the course having certificate then only the results will be prints. for this i am using inner join with many table. Here is the code :

SELECT DISTINCT c.fullname,usr.id, usr.username, usr.email, c.enrolenddate 
FROM m_tl_course AS c 
INNER JOIN m_tl_context AS cx ON c.id = cx.instanceid AND cx.contextlevel = '50' 
INNER JOIN m_tl_role_assignments AS ra ON cx.id = ra.contextid 
INNER JOIN m_tl_role AS r ON ra.roleid = r.id 
INNER JOIN m_tl_user AS usr ON ra.userid = usr.id 
INNER JOIN m_tl_certificate AS ce ON ce.course = c.id 
WHERE r.name = "Student" and ra.timeend = '0'

I have an another table to having data's like the user's who's download their certificate. The table name is m_tl_certification.

In this table having Columns like, user_id ( this the user id), Course_id (this is the course id), cert_date ( this is the certificate download date).

What i want is i want to get the user's who is not download their certicate.

How to get this. please can anyone help me ?

Can you try replacing `where r.name = 'Student' and ra.timeend = '0'` with `LEFT JOIN m_tl_certification cert on usr.id = cert.user_id and c.id = cert.course_id WHERE r.name = 'Student' and ra.timeend = '0' and cert.cert_date is null`? — zedfoxus, Dec 04 '15 at 05:36
One of the query patterns that will work is called an "anti-join". For example: http://stackoverflow.com/questions/9366021/checking-whether-an-item-does-not-exist-in-another-table/9366094#9366094. As another option, you could use a `NOT EXISTS (correlated subquery)` predicate. — spencer7593, Dec 04 '15 at 06:01
how to use not exists with my above query can you please modify my query — Nisanth, Dec 04 '15 at 06:05

score 0 · Answer 1 · answered Dec 04 '15 at 05:45

0

What will be the column value if not downloaded and if downloaded. If the Download date is NULL then in where add Download_Date IS NULL

SELECT DISTINCT c.fullname,usr.id, usr.username, usr.email,c.enrolenddate 
 FROM m_tl_course AS c                                            
 INNER JOIN m_tl_context AS cx ON c.id = cx.instanceid AND cx.contextlevel = '50' 
 INNER JOIN m_tl_role_assignments AS ra ON cx.id = ra.contextid      
 INNER JOIN m_tl_role AS r ON ra.roleid = r.id 
 INNER JOIN m_tl_user AS usr ON ra.userid = usr.id 
 INNER JOIN m_tl_certificate AS ce ON ce.course = c.id INNER JOIN m_tl_certificate AS ce ON ce.course = c.id 
 INNER JOIN m_tl_certification As cee ON cee.user_id = usr.id
 WHERE r.name = "Student" and ra.timeend = '0' and cee.downloadDate IS NULL

answered Dec 04 '15 at 05:45

Raki

535
4
13

no actually if the user download their certificate means then only the m_tl_certification table having entry, otherwisethis table not having entry – Nisanth Dec 04 '15 at 05:47
sounds good then use inner join for m_tl_certification don't put the where condition for cee.downloadDate (which is not required) in first line .i.e SELECT DISTINCT c.fullname,usr.id, usr.username, usr.email,c.enrolenddate add any column of m_tl_certification table. which will return empty so after generating result compare if any column of m_tl_certification table is empty then it is not downloaded. – Raki Dec 04 '15 at 07:13
.i.e for the query which you sent inner join m_tl_certification and get details of certification table if no rows found then it is not downloaded – Raki Dec 04 '15 at 07:15

score 0 · Answer 2 · answered Dec 04 '15 at 05:50

Modify your query with the following

  SELECT DISTINCT c.fullname,usr.id, usr.username, usr.email, c.enrolenddate 
  FROM m_tl_course AS c 
  INNER JOIN m_tl_context AS cx ON c.id = cx.instanceid AND cx.contextlevel = '50' 
  INNER JOIN m_tl_role_assignments AS ra ON cx.id = ra.contextid 
  INNER JOIN m_tl_role AS r ON ra.roleid = r.id 
  INNER JOIN m_tl_user AS usr ON ra.userid = usr.id 
  LEFT JOIN m_tl_certificate AS ce ON ce.course = c.id 
  WHERE r.name = "Student" and ra.timeend = '0'

How to inner join for the resultted query in mysql

2 Answers2