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

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].