How should I write this SQL query? -

i have query this:

select sum(tab1.amount) amount tab1 join tab2   on tab1.lp_id = tab2.id join tab3   on tab3.employee_id = tab2.employee_id tab1.officeid =123   , tab3.space <> 2 

this works fine. want add more filter it. have table tab4, in have columns employee_id, effective_date, salary. ie each employee maintain dates when salary got changed (ie multiple records per employee). want pick employees latest salary greater 10000. how should write this? want this:

select sum(tab1.amount) amount tab1 join tab2    on tab1.lp_id = tab2.id join tab3    on tab3.employee_id            = tab2.employee_id tab1.officeid     =123    , tab3.space <> 2    , (         select salary          (               select *                tab4                 tab4.employee_id  = tab2.employee_id               order effective_d desc               )           rownum = 1         ) > 10000 

i trying add last 2 lines - error because cannot use tab2.employee_id.

how should write this?

how about

select      t.employee_id, t.salary        tab4 t inner join  (select     employee_id, max(effective_date) eff_date                    tab4             group    employee_id) x             on t.employee_id = x.employee_id , t.effective_date = x.eff_date       t.salary > 10000 

this employee salary greater 10000