Ok here's the problem,
I have an employee table that, among other columns, contains these 3 columns:
emp_no, dept_no, job_no
dept_no is foreign key to the departments table and job_no is a foreign key to the jobs table
Now, i want to build a report that returns the number of employees in each department for each job.
So, say i have 2 departments and 3 jobs and in the employee table they are as such:
emp_no dept_no job_no
1 A 1
2 B 1
3 A 2
4 A 1
The output i want should look like this, in a matrix type report:
dept / job: 1 2 3
A 1 2 0
B 1 0 0
Now, unfortunetly, the matrix report ive built does not return a value where I would like it to return a 0, i get:
dept / job: 1 2
A 1 2
B 1
This is my select:
select count(e.emp_no), d.dept_no, j.job_no
from employee e, department d, job j
where e.job_no = j.job_no(+) and
d.dept_no = d.dept_no(+)
group by d.dept_no, j.job_no
Now, i am pretty sure that the solution is to incorporate an inline view to my select statement, but i would like help on how to build such a select statement.
Thanx in advance