MySQL Forums
Forum List  »  Optimizer & Parser

Re: providing a single line for multiple records from a table
Posted by: Rick James
Date: July 24, 2013 09:08AM

select  a.rollno,a.name,a.dt_birth,a.dt_appt,
       b.de_name,sum(case when c.code=1101 then amount else 0 end) as basic,
        sum(case when c.code=6270 then amount else 0 end ) as allowance
    from  empl a,saldesg b , salamount c
    where  a.rollno = c.rollno
      and  a.desg_code = b.de_code ;
-->
select  a.rollno,a.name,a.dt_birth,a.dt_appt,
        b.de_name, 
      ( SELECT  sum(amount)
            FROM  salamount
            WHERE  code=1101
              AND  rollno = a.rollno ) as basic, 
      ( SELECT  sum(amount)
            FROM  salamount
            WHERE  code=6270
              AND  rollno = a.rollno ) as allowance
    from  empl a
    JOIN  saldesg b ON a.desg_code = b.de_code ;

It would help to have one of these compound indexes on salamount:
INDEX(rollno, code)
or
INDEX(code, rollno)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: providing a single line for multiple records from a table
1029
July 24, 2013 09:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.