How should I write this SQL query?

Vote count:

0

I have a query like this: SELECT SUM(tab1.amount) amount FROM tab1 JOIN tab2 ON tab1.lp_id = tab2.id JOIN tab3 ON tab3.employee_id = tab2.employee_id WHERE tab1.officeid =123 AND tab3.space <> 2

This works fine. Now I want to add some more filter to it. Basically I have a table tab4, in which I have columns employee_id, effective_date, salary. ie for each employee we maintain the dates when the salary got changed (ie multiple records per employee). I want to pick only those employees whose latest salary is greater than 10000. How should I write this? Basically I want something like this:

SELECT SUM(tab1.amount) amount FROM tab1 JOIN tab2 ON tab1.lp_id = tab2.id JOIN tab3 ON tab3.employee_id = tab2.employee_id WHERE tab1.officeid =123 AND tab3.space <> 2

AND (select salary from (select * from tab4 where  tab4.employee_id  = tab2.employee_id
  order by effective_d desc)  where rownum = 1)>10000

I am trying to add the last two lines - but I get an error because I cannot use tab2.employee_id. How should I write this?

asked 15 secs ago

user2316569

139