Mysql - Right join not retrieving correct data

mysql right join not working
mysql join
mysql left join multiple tables
mysql full outer join
mysql right join vs left join
mysql join condition
left outer join
mysql right inner join

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

I want the output like this

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

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

MySQL RIGHT JOIN, MySQL RIGHT JOIN, joins two tables and fetches rows based on a NET Database SQL(2003 standard of ANSI) MySQL PostgreSQL SQLite The RIGHT JOIN condition is used to decide how to retrieve rows from table 1st_table. for the value of aval1 200 and 400 because it does not exist in left table. mysql - Right join not retriving correct data. Ask Question Asked 1 year, Thanks for contributing an answer to Database Administrators Stack Exchange!

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

Mysql - Right join not retrieving correct data, You should move the where condition in the ON clause. SELECT SUM(​TIMESTAMPDIFF(MINUTE, A.start_time, A.end_time)) AS prep_time,  The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match. RIGHT JOIN Syntax

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.

MySQL 8.0 Reference Manual :: 13.2.10.2 JOIN Clause, If there is no matching row for the right table in the ON or USING part in a LEFT JOIN , a row with all columns set to NULL is used for the right table. You can use​  RIGHT JOIN is obviously the opposite of LEFT JOIN. The RIGHT JOIN returns all the columns from the table on the right even if no matching rows have been found in the table on the left. Where no matches have been found in the table on the left, NULL is returned.

MySQL: Joins, This MySQL tutorial explains how to use MySQL JOINS (inner and outer) with syntax MySQL JOINS are used to retrieve data from multiple tables. This website would not exist without the advertisements we display and your kind donations. This can be a list of columns, or * to indicate “ all columns. ” which_table indicates the table from which you want to retrieve data. The WHERE clause is optional. If it is present, conditions_to_satisfy specifies one or more conditions that rows must satisfy to qualify for retrieval.

MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS, MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS. Details: Last Joins help retrieving data from two or more database tables. It returns NULL values for records of joined table if no match is found. Sounds  Write a SQL query to find all the details of the nobel winners for the subject not started with the letter 'P' and arranged the list as the most recent comes first, then by name in order. Go to the editor. Sample table: nobel_win

Using Five Types of JOIN in MySQL, For example, you can retrieve query results by including only those rows Note that the full-outer join is not supported by MySQL although you  If you want to retrieve the data from every column in a table, you do not need to specify each column name after the SELECT keyword. Use the asterisk character (*) in place of a column list in a SELECT statement to instruct MySQL to return every column from the specified table. The following query retrieves every column and row from the