[RESOLVED] Wrong result on first call to mysql query?

[dee-u]

I 'm baffled by the behavior of calling this mysql query, the first time calling it it returns an incorrect result as can be seen in the first image. But on succeeding calls the correct result is retrieved. It doesn't matter if I use a plain sql statement or an stored procedure. Anyone who have an idea as to why is this?

string sql = "SELECT X1.studentID, x1.lastName, X1.firstName, X1.middleName, X1.gender, X1.yearlevel, X1.CourseID, X1.FeeTitle, X1.Amount, X2.SubjectTitle, X2.units, X2.subjectcode, X2.feeCost, X2.fee " +
                                     "FROM " +
                                     "(SELECT @row_number1 := CASE WHEN @customer_no1 = studentID THEN @row_number1 + 1 ELSE 1 END AS num1, @customer_no1 := studentID AS CustomerNumber, unifastotherfees.* FROM `enrollmentsystem`.`unifastotherfees`) AS X1 " +
                                     "LEFT JOIN " +
                                     "(SELECT @row_number2 := CASE WHEN @customer_no2 = studentID THEN @row_number2 + 1 ELSE 1 END AS num2, @customer_no2 := studentID AS CustomerNumber, unifastsubjects.* FROM `enrollmentsystem`.`unifastsubjects`) AS X2 " +
                                     "ON X2.studentID = X1.studentID AND X2.num2 = X1.num1";

                    //string sql = "UnifastCORDataElements";
                
                    List<unifastcordataelements> feesCollection = _dataPortal.FetchMany(sql);

Result of first call


Result of succeeding calls

[dee-u]

Solved by adding the highlighted code below.

SELECT 
  X1.studentID,
  x1.lastName,
  X1.firstName,
  X1.middleName,
  X1.gender,
  X1.yearlevel,
  X1.CourseID,
  X1.FeeTitle,
  X1.Amount,
  X2.SubjectTitle,
  X2.units,
  X2.subjectcode,
  X2.feeCost,
  X2.fee 
FROM
  (SELECT 
    @row_number := 
    CASE
      WHEN @customer_no = studentID 
      THEN @row_number + 1 
      ELSE 1 
    END AS num,
    @customer_no := studentID AS CustomerNumber,
    unifastotherfees.* 
  FROM
    `enrollmentsystem`.`unifastotherfees`,(SELECT @customer_no:=0,@row_number:=0) AS t) AS X1 
  LEFT JOIN 
    (SELECT 
      @row_number := 
      CASE
        WHEN @customer_no = studentID 
        THEN @row_number + 1 
        ELSE 1 
      END AS num,
      @customer_no := studentID AS CustomerNumber,
      unifastsubjects.* 
    FROM
      `enrollmentsystem`.`unifastsubjects`,(SELECT @customer_no:=0,@row_number:=0) AS t) AS X2 
    ON X2.studentID = X1.studentID 
    AND X2.num = X1.num;