0

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

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
0

Your problem is in the WHERE clause

WHERE A.user_id = '1'

You are creating correctly the RIGHT JOIN but this condition is forzing to showthe row only if the Log table (renamed as A) is equal to 'Y'. You need to change this condition for example:

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 COALESCE(A.user_id, '1') = '1' 
                GROUP BY A.subject_id

I

0

The condition by left table in WHERE clause (WHERE A.user_id = '1') converts your join to INNER. Move this condition from WHERE clause to 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.