Mysql – Multiple Left Join Fetch Duplicate Data

duplicationjoin;MySQL

I am having duplicates data with this enormous query. 

SELECT        t2.`1st_test` AS agric1, t2.`2nd_test` AS agric2, 
              t2.exam AS agricExam3, t2.result_total AS agricTotal, 
              t2.result_grade AS agricGrade,  t2.result_remark AS agricRemark
FROM          
              result_summary AS t1 
              LEFT  JOIN
                     agricultural_science AS t2 
                     ON t1.student_id = t2.student_id
WHERE        (    t1.student_id     = 'GCU/16/10414' 
              AND t1.result_term    = '1st' 
              AND t1.result_session = '2016/2017' 
              AND t1.result_level   = 'JSS' 
              AND t1.result_class   = '2')

Table Structure

Structure for the Agricultural Science table is like so:

|id|student_id|result_session|result_term|result_arm|
 result_class|result_level|1st_test|2nd_test|exam|result_total|
 result_grade|result_remark|

The table structure for Result_Summary Table look like so:

|id|student_id|result_score|result_average|result_term|
 result_session|result_level|result_class|result_arm

Data that match the criteria exist in some tables only, while others contains two rows each with the same Student_ID but different Session. When the query is executed I get enormous duplicates and even data that does not match the 2016/2017 session.

Please, how can I fix this?

Best Answer

You're joining only on 'student_id', when you need to join using, at least, also result_session, but most probably all of result_term, result_session, result_level,result_class,result_arm.

Try either:

SELECT        
    t2.`1st_test` AS agric1, t2.`2nd_test` AS agric2, 
    t2.exam AS agricExam3, t2.result_total AS agricTotal, 
    t2.result_grade AS agricGrade,  t2.result_remark AS agricRemark
FROM            
    result_summary AS t1 
    LEFT JOIN agricultural_science AS t2 
        ON t1.student_id = t2.student_id
           /* This is what I guess you're missing */
           AND t1.result_session = t2.result_session 
WHERE        
    t1.student_id = 'GCU/16/10414' 
    AND t1.result_term = '1st' 
    AND t1.result_session = '2016/2017' 
    AND t1.result_level = 'JSS' 
    AND t1.result_class = '2'

or

SELECT        
    t2.`1st_test` AS agric1, t2.`2nd_test` AS agric2, 
    t2.exam AS agricExam3, t2.result_total AS agricTotal, 
    t2.result_grade AS agricGrade,  t2.result_remark AS agricRemark
FROM            
    result_summary AS t1 
    LEFT JOIN agricultural_science AS t2 
        ON t1.student_id = t2.student_id
           /* Or most probably you're missing all of this */
           AND t1.result_session = t2.result_session
           AND t1.result_term = t2.result_term
           AND t1.result_level = t2.result_level
           AND t1.result_arm = t2.result_arm
WHERE        
    t1.student_id = 'GCU/16/10414' 
    AND t1.result_term = '1st' 
    AND t1.result_session = '2016/2017' 
    AND t1.result_level = 'JSS' 
    AND t1.result_class = '2'

As a side comment: this data structure for a database could very easily be improved...

My original trial (on a different DB than mySQL)

WITH agricultural_science(id, student_id, result_session,
    result_term, result_arm, result_class, result_level,
    "1st_test", "2nd_test", exam, result_total, result_grade, result_remark)
AS
(
VALUES
 (1, 'GCU/16/10414', '2016/2017', 
    '1st', 'arm', '2', 'JSS', 
    93, 92, 'exam', 'result_total', 'result_grade', 'result_remark'), 
 (1, 'GCU/16/10414', '2015/2016', 
    '1st', 'arm', '2', 'JSS', 
     99, 23, 'exam', 'result_total', 'result_grade', 'result_remark')

)
, result_summary (id, student_id, result_score, result_average,
    result_term, result_session, result_level, result_class, result_arm
) AS
(
VALUES
 (1, 'GCU/16/10414', 99.1, 90.1, 
    '1st', '2016/2017', 'JSS', '2', 'arm')
)

SELECT        
    t2."1st_test" AS agric1, t2."2nd_test" AS agric2, 
    t2.exam AS agricExam3, t2.result_total AS agricTotal, 
    t2.result_grade AS agricGrade,  t2.result_remark AS agricRemark
FROM            
    result_summary AS t1 
    LEFT JOIN agricultural_science AS t2 
        ON t1.student_id = t2.student_id
           AND t1.result_session = t2.result_session
           AND t1.result_term = t2.result_term
           AND t1.result_level = t2.result_level
           AND t1.result_arm = t2.result_arm
WHERE        
    t1.student_id = 'GCU/16/10414' 
    AND t1.result_term = '1st' 
    AND t1.result_session = '2016/2017' 
    AND t1.result_level = 'JSS' 
    AND t1.result_class = '2' ;
Related Question