Re: simple select statement inquiry

On Wed, 5 Jun 2002, Ludwig Lim wrote:

> > desired output
> > empno peer superior
> > 1000 John Smith Henry Dunst
> > 2000 Juan dela Cruz Pepe Smith
>
> Create a stored function that will return the fullname
> of the "peer" or "superior" given an employee number

Or, much faster, in a single statement:

SELECT e.empno,
p.fname || ' ' || p.lname AS peer,
s.fname || ' ' || s.lname AS superior
FROM emp1 AS e,
emp2 AS p,
emp2 AS s
WHERE e.peerno = p.empno
AND e.supno = s.empno

This assumes that every person in emp will have non-null values for the
peer and superior columns. If someone didn't, they wouldn't appear in this
input. To fix this, you could re-write this using LEFT OUTER JOINs from
emp1 to the two emp2's. This also assumes that neither fname or lname will
be null (if either or both were, the fullname would be null). You can fix
this with a COALESCE.

HTH.

- J.
--

Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant