Outer Join at group level
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Outer Join at group level

  1. #1
    Join Date
    Sep 2001
    Posts
    10

    Question

    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

  2. #2
    Join Date
    Jun 2002
    Location
    Denver
    Posts
    54
    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
  •  


Click Here to Expand Forum to Full Width