Outer Join at group level

[Mlimaye]

Hi,

Is it possible to do an outer join at group level? for example

Dept table --->
Deptno Deptname
-------- ------------
10 abc
20 def
30 xyz

StnEmp table --->
Station Deptno NoOfEmp
-------- -------- -----------
ATL 10 5
ATL 30 6
NYC 20 8

If i do following query :

Select s.Station, s.Deptno, d.Deptname, s.NoofEmp
from Dept d, StnEmp s
where d.deptno = s.deptno (+)

I get the output :

Station Deptno Deptname NoofEmp
-------- -------- ---------- -----------
ATL 10 abc 5
NYC 20 def 8
ATL 30 xyz 6

What i want is output like following. I want outer join to be applied at group level (Station) i.e. query should display all departments and no of employees (if any) for all stations.

Station Deptno Deptname NoofEmp
-------- -------- ---------- ----------
ATL 10 abc 5
ATL 20 def
ATL 30 xyz 6
NYC 10 abc
NYC 20 def 8
NYC 30 xyz

any ideas?

Rgds

Mlimaye

[shibha]

Try this ( because I have not, I'm guessing this should work ).


Create view all_stations for
select distinct station
from
stnemp;

create view all_stations_depts for
select station, deptno, deptname
from
all_stations, dept;


select a.Station, a.Deptno, a.Deptname, s.NoofEmp
from
all_stations_depts a,
StnEmp s
where
a.station = s.station(+) and
a.deptno = s.deptno (+);