SQL & PL/SQL

Hi,

I have a table like this

Emp_no

Emp_name

job_id

Dept_id

phone_number

Manager_id

etc....

I need to select common job_ids from department_id <=50 and department_id > 50.

i.e I need to compare the all job_ids of departments 1  thru 50  to departments 50 thru infiniti.

I have this so far, but not sure whether this is correct or is there another efficient way to achieve this.

select emp_name, job_id, dept_id from

(select emp_name, job_id, dept_id from emp where dept_id <=50) t1,

(select emp_name, job_id, dept_id from emp where dept_id > 50 ) t2  where  t1.job_id = t2.job_id;

If I don't put select t1.emp_name, t1,job_id etc in line 1 I get ambiguous column error.

If I put t1.emp_name,.... in line 1, then the result from the first table like t1 only is selected, the rows from t2 are not returned in result set. What am I doing wrong?

Please point me in right direction.

Thank you,