CODEWITHSUNDEEP
mysqlcodeignitersum
user_777
user_777

Reputation: 805

fetching data from the table

Here am having two tables namely rent and student_hostel.the rent table looks like this 

    id   date      stud_id  paid    balance

    18  10-2016       94    15000   15000
    19  10-2016       94    10000   5000
    20  10-2016       96    25000   5000
    21  10-2016       96    5000    0

my student_hostel table looks like this..

     id   first_name  last_name    stud_id     admit_date   hostel   class  room bed status

     94     ss        ff          PHBH00094     01-10-2016   12        16   115  501A    P
     96    maltu      uv          PHBH00096     01-10-2016   12        16   115  501C    p

In order to get the last inserted stud_id's balance i used my code like this,

public function rent_outstanding($hos,$dt)
{
    $sql = "select s.stud_id ,s.admit_date ,s.class,first_name,sum(paid) as rt_paid,balance,rt.stud_id 
            from student_hostel s, rent rt  where s.id=rt.stud_id and hostel=? and rt.date=? 
            and rt.id = (select max(id) from rent r where r.stud_id = rt.stud_id and r.date='$dt') 
            and status!= 'G' and status!= 'R'  GROUP BY rt.stud_id";
    $query=$this->db->query($sql, array($hos, $dt));
    return $query;
}

the problem am facing here is i was not able to sum the values comes under the paid columns of same stud_id. the output am getting is like this

SI.No   STUDENT ID   NAME    RENT    PAID    BALANCE
 1      PHBH00094     Ss    30000    10000    5000  
 2      PHBH00096    Maltu  30000     5000      0

the desired output i need to get is like this

SI.No   STUDENT ID   NAME    RENT    PAID    BALANCE
 1      PHBH00094     Ss    30000   25000     5000  
 2      PHBH00096    Maltu  30000   30000       0

Upvotes: 1

Views: 62

Answers (1)

Razib Al Mamun
Razib Al Mamun

Reputation: 2711

Your final solved query is : 

select s.stud_id ,s.admit_date ,s.class,first_name,sum(paid) as rt_paid, 
(select r.balance from rent r where r.stud_id = rt.stud_id and r.date='$dt' order by r.id desc limit 1) as balance ,rt.stud_id 
from student_hostel s join rent rt  on s.id=rt.stud_id where hostel=? and rt.date=? 
and status!= 'G' and status!= 'R'  GROUP BY s.stud_id

I have removed this line in your existing query rt.id = (select max(id) from rent r where r.stud_id = rt.stud_id and r.date='$dt')

your (select max(id) from rent r where r.stud_id = rt.stud_id and r.date='$dt') means all time getting only one last row rt.id like 19 (for PHBH00094) or 21 (for PHBH00096). that's why you getting all time one last calculation.

Upvotes: 1

Related Questions