Re: Join Logical file using the same physical file twice

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