Bring missing items with LEFT JOIN

1

Starting from two tables

lessons

lessons.lessons_id, lessons.lesson_number, lessons.course_id entre otras 

and lessons_completed

lessons_completed.lessons_number, lessons_completed.user_id, 
lessons_completed.completed

With the following code, LEFT JOIN works like a charm, however, delete all the records in the lessons_completed table and as it was obvious I stopped showing the exercises in the lessons table , and if I add user records in the lessons_completed table, the exercises appear with their respective check, that's fine, but it does not show me the exercises that have not yet completed the user.

to show the check I only use an if to the column:

if $lesson[completed]==1 {
muestro el check 
}  else { 
   NO OK
}

In the lessons_completed.completed column there are only values 1 the 0 does not exist because the record is created when the user clicks Completed with an insert

I will add the code as it was after your recommendations

     public function get_lessons_completed($args = array()) {
    global $db;
    $user_id = !isset($args['user_id']) ? null : $args['user_id'];
    $course_id = !isset($args['course_id']) ? null : $args['course_id'];
    $lessons_completed = array();

            $get_lessons_completed = $db->query
            ("SELECT
                 lessons.lesson_id,
                 lessons.course_id,
                 lessons.lesson_number,
                 lessons.lesson_intro,
                 lessons.lesson_text,
                 lessons.lesson_date,
                 lessons.lesson_prev,
                 lessons.lesson_top,
                 LC.lesson_number,
                     LC.completed
                 FROM lessons

                 LEFT JOIN lessons_completed LC

                 ON lessons.lesson_number = LC.lesson_number

               WHERE lessons.course_id = 2 AND LC.user_id= 2 ORDER BY 
            lessons.lesson_number

            ") or _error(SQL_ERROR_THROWEN);

          if($get_lessons_completed->num_rows > 0) {
            while($lesson_completed = $get_lessons_completed->fetch_assoc()) {

            $lessons_completed[] = $lesson_completed;
        }
     }
      return $lessons_completed;
     }

Up to here all excellent, Now the question is: how can I do or what I need to bring the exercises from the table lessons that the user has not yet completed? without duplicating the records with which the user already completed?

Here the results are shown with the code above, but there are for example in this COURSE ID in the table lessons other 6 exercises that I have tried to bring, but they are duplicated with those already complete the user if I make changes to the function

Try bringing the nulls with a:

OR NULL

and the result is that they are duplicated

I already tried changing INNER and RIGTH in my desperation but the result is the same, it brings me the same data in the same way as it is currently.

Maybe the ideal would be adding another JOIN but I'm not sure how to do it.

I hope friends can help me. I think I've never faced a situation I can not solve on my own, those JOINs bring me dizzy.

    
asked by Antonio Fernandez 14.08.2018 в 18:15
source

3 answers

0

Solution:

    SELECT
             lessons.lesson_id,
             lessons.course_id,
             lessons.lesson_number,
             lessons.lesson_intro,
             lessons.lesson_text,
             lessons.lesson_date,
             lessons.lesson_prev,
             lessons.lesson_top,
             LC.completed
     FROM lessons
             LEFT JOIN lessons_completed LC
                 ON
                 lessons.lesson_number = LC.lesson_number
                 AND lessons.course_id = 2
                 AND LC.user_id= 2 ORDER BY lessons.lesson_number

Explanation:

The LEFT JOIN allows you to bring all the records from the left table regardless of whether there is a match in the right table. The problem arises when adding the clause WHERE . The query keeps bringing all the records of the left table, but then they are filtered by the conditions of WHERE which means that not all the records are shown.

The solution is to move the conditions of the WHERE to be part of the JOIN . In this case the query says: "Join the two tables based on these conditions, but I also want the records of the left table that do not comply with the same"

    
answered by 14.08.2018 / 19:26
source
0

This is the result with the recommendation of @alancfm for some reason I do not take the value of the column lessons.lesson_number to show it

    
answered by 14.08.2018 в 18:43
0

Final code to many I'm sure will serve you.

    public function get_lessons_completed($args = array()) {
    global $db;
    $user_id = !isset($args['user_id']) ? null : $args['user_id'];
    $course_id = !isset($args['course_id']) ? null : $args['course_id'];
    $lessons_completed = array();

            $get_lessons_completed = $db->query
            ("SELECT
                 lessons.lesson_id,
                 lessons.course_id,
                 lessons.lesson_number,
                 lessons.lesson_intro,
                 lessons.lesson_text,
                 lessons.lesson_date,
                 lessons.lesson_prev,
                 lessons.lesson_top,
                     LC.completed
                 FROM lessons

                 LEFT OUTER JOIN lessons_completed LC
                     ON
                     lessons.lesson_number = LC.lesson_number
                     AND lessons.course_id = 2
                     AND LC.user_id= 2 ORDER BY lessons.lesson_number

            ") or _error(SQL_ERROR_THROWEN);

    if($get_lessons_completed->num_rows > 0) {
        while($lesson_completed = $get_lessons_completed->fetch_assoc()) {

            $lessons_completed[] = $lesson_completed;
        }
    }
    return $lessons_completed;
    }

thanks to @alancfm and @M. Gress

This is the final result

It already correctly displays all lessons records without being duplicated and respects the value of lessons_completed.completed

    
answered by 14.08.2018 в 19:00