MySQL Forums
Forum List  »  Optimizer & Parser

providing a single line for multiple records from a table
Posted by: raja chacham
Date: July 23, 2013 09:56AM

i have 3 tables as follows:
table1: empl
roll no , name, dt_birth,dt_appt,desg_code

123 , raja,01/07/1971, 01/01,1996

table2: sal_amount
roll no , year , month , code , amount
123 , 2013 , 06, 1101 , 15000
123 2013 06 6270 500

table 3 ; saldesg

de_code, de_name

I want to get a single entry for each rollno as

roll no , name, dt_of_birth ,dt_appt{from table1},de_name {from table 3}, amount in 1101 as basic, amount in 6270 as allowance {from table 2}.

I have written the query which is taking a quite long time.
select a.rollno,,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 ;

Options: ReplyQuote

Written By
providing a single line for multiple records from a table
July 23, 2013 09:56AM

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.