sql - How do I filter out records from Table B based on a field in Table A? -

sql - How do I filter out records from Table B based on a field in Table A? -

because may relevant, dbms using teradata.

i have 2 tables named table , table b. table contains employee personal , company info (company-issued id, name, pay type, etc.), , table b contains info concerning jobs each employee has held or holding. primary key both tables employee's company-issued id number.

currently, trying write query partly entails retrieving employee's current position. seems should easy, since table b stores "effective" dates , "expiration" dates each job, however, there problem. in company work for, there 2 different systems used track employees. each scheme identified human resources code. codes each scheme , employees included in scheme follows:

if work in retail store in non-managerial position, scheme identified code "1". if work in retail store in managerial position or work corporate, scheme identified code "2".

each record in table b has field indicating hr scheme particular job tied to.

occasionally, working @ store in managerial position may need have record in scheme identified code "1" in order have access store-level systems need work. when happens, job record added table b employee tied scheme "1". in such cases, particular employee, 2 current records in table b correspond actual position within company while other additional record created them. additionally, these records have "effective" dates later "effective" date employee's actual job.

in query, need obtain each employee's actual job table b. known every genuine scheme "1" employee hourly, while in scheme "2" salaried. table has field indicating each employee's current pay type.

so, basically, each employee included in query, need filter out cases employee has scheme "1" record in table b while table indicates employee salaried , not include them when calculating employee's actual current position.

i apologize "wall of text" here, sense problem complex, wanted sure aware of relevant details.

admittedly, still learning sql, if there constructs or aggregate functions can help, please allow me know.

thank time.

sql teradata