Mysql - Right join not retrieving correct data

user_6618283 0
Viewed 48

I am storing the user activity in the table by start and end time , now i want to get all records from my subject table and matched records from log table.

This is my subject table

enter image description here

This is my log table

enter image description here

I want the output like this

enter image description here

I tried using some code but the records that only matched with log table is return as record, This is what i tried. Any help is appreciable.

SELECT SUM(TIMESTAMPDIFF(MINUTE, A.start_time, A.end_time)) AS prep_time,
                B.subject_name,
                A.subject_id
            FROM prep_learn_log A
            RIGHT JOIN prep_subject B ON A.subject_id = B.subject_id 
                AND B.active = 'Y'
            WHERE A.user_id = '1' GROUP BY A.subject_id
outer-join mysql
3 Answers

You can try below by putting A.user_id = '1' in On Clause

SELECT SUM(TIMESTAMPDIFF(MINUTE, A.start_time, A.end_time)) AS prep_time,
       B.subject_name,
       A.subject_id
       FROM prep_learn_log A
       RIGHT JOIN prep_subject B ON A.subject_id = B.subject_id 
       AND B.active = 'Y' and A.user_id = '1' 
       GROUP BY A.subject_id,B.subject_name
user_69148640

You should move the where condition in the ON clause 

SELECT SUM(TIMESTAMPDIFF(MINUTE, A.start_time, A.end_time)) AS prep_time,
            B.subject_name,
            A.subject_id
        FROM prep_learn_log A
        RIGHT JOIN prep_subject B ON A.subject_id = B.subject_id 
            AND B.active = 'Y' AND A.user_id = '1' 
 GROUP BY A.subject_id

the use of right joined column in where condition work as an inner join

user_35223120

How's this?

SELECT IFNULL(SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(b.end_time,b.start_time)))),"00:00:00") AS prep_time, a.subject_name,a.subject_id FROM prep_subject a LEFT JOIN prep_learn_log b ON a.subject_id = b.subject_id AND a.active = 'Y' GROUP BY a.subject_id ORDER BY a.subject_id;

I changed to LEFT JOIN and TIMEDIFF instead because I couldn't get TIMESTAMPDIFF working on my side.

user_109106920
Related