oracle sql one is to many join

Question

I have four tables that I wanted to join to get the count of emp that has the same dir and type as follows:

directory

first_char   dir
-------------------
0000         Dir0
1111         Dir1
2222         Dir2

emp_type

type
------
typeA
typeB
typeC

assigned_num

no      emp_id
-------------------------
1111A   1
1111B   2
0000A   3
1111C   4
2222A   5
2222B   6
0000B   7

emp

id      type
--------------------
1       typeA
2       typeB
3       typeA
4       typeA
5       typeA    
6       typeB
7       typeA

Expected output

dir    type    # of emp
------------------------
Dir0   typeA   2          --> emp_id 3 and 7    
Dir0   typeB   0
Dir0   typeC   0
Dir1   typeA   2          --> emp_id 1 and 4
Dir1   typeB   1          --> emp_id 2
Dir1   typeC   0
Dir2   typeA   1          --> emp_id 5
Dir2   typeB   1          --> emp_id 6
Dir2   typeC   0

I tried:

SELECT dire.dir, etype.type, COUNT(anum.emp_id)
FROM emp_type etype
JOIN emp empl
ON etype.type = empl.type 
JOIN assigned_num anum 
ON empl.id = anum.emp_id
JOIN directory dire
ON anum.no LIKE dire.first_char  || '%'
GROUP BY dire.dir, etype.type;

but it wont display dir and type that has 0 emp count

Answer 1

Solution to your peoblem:

SELECT dir.dir, et.type,COUNT(emp.id) No_Of_Emp
FROM emp_Type et
CROSS JOIN Directory dir
LEFT JOIN assigned_num an
ON an.no LIKE dir.first_char  || '%' 
LEFT JOIN emp
ON et.type = emp.type
AND an.emp_id = emp.id
GROUP BY dir.dir, et.type
ORDER BY dir.dir, et.type

OUTPUT:

DIR      TYPE   NO_OF_EMP
=========================
Dir0    typeA    2
Dir0    typeB    0
Dir0    typeC    0
Dir1    typeA    2
Dir1    typeB    1
Dir1    typeC    0
Dir2    typeA    1
Dir2    typeB    1
Dir2    typeC    0

Follow the link to the demo:

http://sqlfiddle.com/#!4/e2dd9/59