Multiple Left Join Fetch Duplicate Data

Question

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?

• joanolo: One thing really looks strange: all your conditions on WHERE are columns of t1, I'd really need some practical example where you actually get a t1.result_session other than '2016/2017'; because I think this is impossible [or a typo].
• BlackPearl: @joanolo The current query posted here is what I use to test when I found out that the query is returning duplicates. That's why there is 2016/2017 etc I normally use e.g result_session = @session for PDO then supply the criteria via the codes.
• joanolo: Can you make public your table definitions and table data? (or the minimum sample that shows the error)
• joanolo: If you really do get a row where the t1.result_session = '2016/2017'condition is not met, I'd check for some index corruption in the database. Otherwise, I don't see how you could get it.
• BlackPearl: @joanolo I have not set index on any column yet

Answer 1

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' ;