Re: Join Logical file using the same physical file twice

[rob@xxxxxxxxx]

with
> select * from qtemp.emp
..1....+....2....+....3
EMPNBR          MASTER 
     1               2 
     2               3 
     3               0 
*  End of data  *******

> select * from qtemp.empemail
..1....+....2....+....3.
EMPNBR   EMAIL 
     1   one@xxxxxxxxx 
     2   two@xxxxxxxxx 
     3   three@xxxxxxxxx
*  End of data  ********


> create view qtemp.empemailv as( 
  select emp.empnbr, emp.master, 
    em1.email as employeeemail,  em2.email as masteremail 
  from qtemp.emp as emp left outer join qtemp.empemail as em1 
                          on emp.empnbr=em1.empnbr 
                        left outer join qtemp.empemail as em2 
                          on emp.master=em2.empnbr and emp.master<>0)

> select * from qtemp.empemailv 
..1....+....2....+....3....+....4....+....5....+....6....+....7....
EMPNBR          MASTER   EMPLOYEEEMAIL              MASTEREMAIL 
     1               2   one@xxxxxxxxx              two@xxxxxxxxx 
     2               3   two@xxxxxxxxx              three@xxxxxxxxx
     3               0   three@xxxxxxxxx            - 
*  End of data  ******** 

You might want to throw in a IFNULL or COALESCE on that last row and give 
it a default value.

Rob Berendt