you'll live as long as IT live....

Check the following query... SELECT T1.empcode,T1.[basic Salary],B.Bonus,T.Transport FROM Table1 T1 INNER JOIN (SELECT Amount As Bonuus, empcode FROM Table2 WHERE Additions = 'Bonus') B ON B.empcode = T1.empcode INNER JOIN (SELECT Amount AS Transport, empcode FROM Table2 WHERE Additions = 'Transport') T ON T.empcode = T1.empcode Adjust the inner join to left join if required... Thanks