title: Left Outer Join (MySQL) Issues, what's wrong with this query?
body: <p>I am having five tables in mySQL and i am working on student fees module but i am having some problems on query so i am unable to get appropriate result so please help me I would greatly appreciate it if you kindly give me some feedback on this Query.</p>
  
  <p>1.a )class_details Table Create</p>
  
  <pre><code>CREATE TABLE `class_details` 
  (`class_id_pk` int(11) NOT NULL AUTO_INCREMENT
  ,`class_name`varchar(200) NOT NULL
  ,`session` varchar(50) DEFAULT NULL
  ,`class_status` varchar(50) DEFAULT NULL
  ,PRIMARY KEY (`class_id_pk`)
  ,UNIQUE KEY `UNIQUE` (`class_name`,`session`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>1.b) class_details Insert</p>
  
  <pre><code>insert  into `class_details`
  (`class_id_pk`,`class_name`,`session`,`class_status`) 
  VALUES 
  (1,'1st','2016-2017',NULL)
  ,(2,'2nd','2016-2017',NULL)
  ,(3,'3rd','2016-2017',NULL);
  </code></pre>
  
  <p>2.a) feedetails Table Create</p>
  
  <pre><code>CREATE TABLE `feedetails` 
  (`section_id_fk` int(50) NOT NULL
  ,`fees` varchar(30) DEFAULT NULL
  ,PRIMARY KEY (`section_id_fk`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>2.b) feedetails Insert</p>
  
  <pre><code>insert  into `feedetails`(`section_id_fk`,`fees`) 
  values 
  (1,'1000')
  ,(2,'2000')
  ,(3,'3000')
  ,(4,'4000')
  ,(5,'5000')
  ,(6,'6000');
  </code></pre>
  
  <p>3.a) section_details Create</p>
  
  <pre><code>CREATE TABLE `section_details` 
  (`section_id_pk` int(11) NOT NULL AUTO_INCREMENT
  ,`class_id_fk` int(11) NOT NULL
  ,`section_name` varchar(50) NOT NULL
  ,`section_status` varchar(50) DEFAULT NULL
  ,PRIMARY KEY (`section_id_pk`,`class_id_fk`,`section_name`)
  ,UNIQUE KEY `UNIQUE` (`class_id_fk`,`section_name`)
  ,CONSTRAINT `FK_section_details` FOREIGN KEY (`class_id_fk`) REFERENCES `class_details` (`class_id_pk`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>3.b) section_details Insert</p>
  
  <pre><code>insert  into `section_details` (`section_id_pk`,`class_id_fk`,`section_name`,`section_status`) 
  values 
  (1,1,'A',NULL)
  ,(2,2,'A',NULL)
  ,(3,3,'A',NULL);
  </code></pre>
  
  <p>4.a) student_fee</p>
  
  <pre><code>CREATE TABLE `student_fee` 
  ( `sr_no` int(200) NOT NULL AUTO_INCREMENT
  ,`scholar_no`int(50) NOT NULL
  ,`paid_amount` int(200) DEFAULT NULL
  ,`due_amount` int(200) DEFAULT NULL
  ,`fee_date` date DEFAULT NULL
  ,`section_id_fk` int(50) DEFAULT NULL
  ,PRIMARY KEY (`sr_no`)
  ,KEY `FK_student_fee`(`section_id_fk`)
  ,CONSTRAINT `FK_student_fee` FOREIGN KEY (`section_id_fk`) REFERENCES `section_details` (`section_id_pk`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>4.b) student_fee Insert</p>
  
  <pre><code>insert  into student_fee` (`sr_no`,`scholar_no`,`paid_amount`,`due_amount`,`fee_date`,`section_id_fk`) 
  values 
  (3,5,800,200,'2016-06-16',1)
  ,(4,29,1000,0,'2016-06-16',1)
  ,(5,5,200,0,'2016-06-16',1);
  </code></pre>
  
  <p>5.a) student_details Create</p>
  
  <pre><code>CREATE TABLE `student_details` 
  (`scholar_no` int(30) NOT NULL
  ,`fname` varchar(30) DEFAULT NULL
  ,`lname` varchar(30) DEFAULT NULL
  ,`stu_class` varchar(30) DEFAULT NULL
  ,`rte` varchar(30) DEFAULT NULL
  ,`active` varbinary(10) DEFAULT NULL
  ,PRIMARY KEY (`scholar_no`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>5.b) student_details Insert</p>
  
  <pre><code>insert  into `student_details` (`scholar_no`,`fname`,`lname`,`stu_class`,`rte`,`active`) 
  values 
  (5,'KP','PK','1','N','y')
  ,(29,'Abc','Xyz','1','N','y');
  </code></pre>
  
  <p>Above i have mentioned all the Table details which are used for this query.
  First time in student_details table stu_class=1 and the fees will be 1000. when i insert some values in student_fee and use my query then the result is correct but when i insert some of amount to another student in student_fee then the query added the paid amount to the same student which is not correct i want to show the inserted fees for those student who actually inserted..</p>
  
  <pre><code>SELECT 
  student_details.scholar_no
  ,student_details.fname
  ,student_details.lname
  ,student_details.stu_class
  ,feedetails.fees
  ,class_name
  ,section_name
  ,IF(sssf.paid_amount IS NULL,0,sssf.paid_amount) AS paid_amount 
  FROM 
      (student_details 
      LEFT OUTER JOIN feedetails 
          ON student_details.stu_class = feedetails.section_id_fk
      ) 
  LEFT OUTER JOIN 
      (SELECT 
      scholar
      , SUM(pa) AS paid_amount
      , SUM(pva) AS prev_paid_amount
      ,SUM(da) AS due_amount
      , SUM(dva) AS prev_due_amount
      ,section_id_fk
      ,fee_date
      ,stu_class 
      FROM 
          (SELECT 
          scholar
          ,CASE WHEN section_id_fk = stu_class THEN paid_amount ELSE 0 END AS pa
          ,CASE WHEN section_id_fk != stu_class THEN paid_amount ELSE 0 END AS pva
          ,CASE WHEN section_id_fk = stu_class THEN due_amount ELSE 0 END AS da
          ,CASE WHEN section_id_fk != stu_class THEN due_amount ELSE 0 END AS dva
          ,due_amount
          ,paid_amount
          ,section_id_fk
          ,fee_date
          ,stu_class 
          FROM 
              (SELECT 
              scholar
              ,due_amount
              ,SUM(paid_amount) AS paid_amount
              ,section_id_fk
              ,fee_date 
              FROM 
                  (SELECT 
                  student_fee.due_amount AS due_amount
                  ,student_fee.paid_amount AS paid_amount
                  ,student_fee.scholar_no AS scholar
                  ,section_id_fk
                  ,fee_date 
                  FROM student_fee 
                  ORDER BY STR_TO_DATE(fee_date,'%Y-%m-%d')DESC
                  ) AS kkk 
              GROUP BY kkk.scholar,section_id_fk ORDER BY scholar
          ) AS k 
          LEFT OUTER JOIN student_details sd 
              ON k.scholar = sd.scholar_no
      ) AS lk
  ) AS sssf 
  ON student_details.scholar_no = sssf.scholar 
  LEFT OUTER JOIN 
      (SELECT * 
      FROM section_details AS sd 
      LEFT OUTER JOIN class_details cd 
          ON sd.class_id_fk = cd.class_id_pk
      ) AS sc 
  ON student_details.stu_class = sc.section_id_pk 
  WHERE student_details.active = 'y' AND rte = 'N'
  </code></pre>