0

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?

  • 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]. – joanolo Dec 31 '16 at 13:32
  • @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. – BlackPearl Dec 31 '16 at 13:34
  • Can you make public your table definitions and table data? (or the minimum sample that shows the error) – joanolo Dec 31 '16 at 13:35
  • 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. – joanolo Dec 31 '16 at 13:38
  • @joanolo I have not set index on any column yet – BlackPearl Dec 31 '16 at 13:50
2

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

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.