1

Need Help in this SQL

SELECT mdl_course_completions.course, mdl_course.fullname, mdl_course_completions.userid, mdl_user_enrolments.userid, mdl_user_enrolments.enrolid 
FROM mdl_course_completions 
LEFT JOIN mdl_course ON mdl_course_completions.course = mdl_course.id 
LEFT JOIN mdl_user_enrolments ON mdl_course_completions.userid = mdl_user_enrolments.userid 
WHERE mdl_user_enrolments.enrolid IN
      (SELECT mdl_enrol.id from mdl_enrol where mdl_enrol.courseid = mdl_course_completions.course)

In mdl_course_completions table, We have 3122 records of which 1471 are relevant records in mdl_user_enrolment. For the balance 3122-1471 (1651), it should still extract the data for mdl_course_completions.course, mdl_course.fullname & mdl_course_completions.userid with blank fields under mdl_user_enrolments.userid, mdl_user_enrolments.enrolid

This system does not have proper Foreign Keys defined, hence the usage of Relationship is not maintained similarly across tables, hence to get the records from mdl_user_enrolment need to have the select clause.

Can someone help with a solution. Appreciate the same.

Prakash PV
  • 19
  • 2
  • 1
    Welcome to SO. Please see [Why should I provide an MCRE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry May 22 '20 at 15:43
  • 1
    Please [edit] your question to explain what you mean by *This system is not defined as a proper RDBMS as it is a opensource,*. – O. Jones May 22 '20 at 15:47
  • 1
    'This system is not defined as a proper RDBMS as it is a opensource' - so what as long as these your tables exist in a DB you can join them if there are relationships between them - it doesn't matter if no indexes or FKs exist (expect for performance). Your query looks like it might work BTW - if not what problem are you having. – P.Salmon May 22 '20 at 15:49
  • This is a faq. Before considering posting please read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. – philipxy May 22 '20 at 19:03

1 Answers1

0

When you mentioned a column from a LEFT JOINed table in a WHERE clause, you converted the LEFT JOIN to an ordinary inner JOIN, and so suppressed the rows that don't match the ON clause from your result set.

  ...
  LEFT JOIN mdl_user_enrolments
            ON mdl_course_completions.userid = mdl_user_enrolments.userid 
 WHERE mdl_user_enrolments.enrolid IN
         (SELECT mdl_enrol.id 
            from mdl_enrol
           where mdl_enrol.courseid = mdl_course_completions.course)

Try refactoring the WHERE as another LEFT JOIN like this

  ...
  LEFT JOIN mdl_user_enrolments
            ON mdl_course_completions.userid = mdl_user_enrolments.userid 
  LEFT JOIN md_enrol 
            ON md_enrol.courseid = mdl_course_completions.course

and see if you get the correct result set.

And, with respect, try telling the MySQL, MariaDB, and PostgreSQL open source development teams that their products aren't proper RDBMSs. I suspect they will disagree.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Sorry I did not mean MySQL being a RDBMS or not, in fact what I meant was the Application did not use Foreign Key because of which the usage of Joins had to deal with multiple tables. – Prakash PV May 24 '20 at 04:19
  • Thank you all, Based on Philips answer added an AND clause to the Left Join and it worked. – Prakash PV May 24 '20 at 09:14