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@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant