-
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
-
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 (+);
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|