CODEWITHSUNDEEP
mysqlouter-join
sooraj s pillai
sooraj s pillai

Reputation: 916

Mysql - Right join not retrieving correct data

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

Upvotes: 3

Views: 55

Answers (3)

FanoFN
FanoFN

Reputation: 7114

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.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

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

Upvotes: 2

Fahmi
Fahmi

Reputation: 37473

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

Upvotes: 1

Related Questions