Обсуждение: simple select statement inquiry

table1
empno varchar(9) not null
peer varchar(9) not null references table2(empno)
superior varchar(9) not null references table2(empno)

table2
empno varchar(9)
firstname varchar(20)
lastname varchar(20)

what i want to do is get all entries in table 1 and transform peer and
superior fields (which contains empno) into their respective firstname +
lastname in one query

desired output
empno             peer                   superior
1000        John Smith        Henry Dunst
2000        Juan dela Cruz        Pepe Smith

TIA
joseph

--- Joseph Syjuco <joseph@asti.dost.gov.ph> wrote:
> table1
> empno varchar(9) not null
> peer varchar(9) not null references table2(empno)
> superior varchar(9) not null references
> table2(empno)
> 
> table2
> empno varchar(9)
> firstname varchar(20)
> lastname varchar(20)
> 
> what i want to do is get all entries in table 1 and
> transform peer and
> superior fields (which contains empno) into their
> respective firstname +
> lastname in one query
> 
> 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

CREATE OR REPLACE FUNCTION get_name(VARCHAR(9))
RETURNS VARCHAR(50)
AS'
DECLARE  v_empno ALIAS FOR $1;  v_last VARCHAR(20);  v_first VARCHAR(20);  v_fullname VARCHAR(50);
BEGIN  SELECT firstname,lastname  INTO v_first,v_last  FROM table2  WHERE empno=v_empno;  v_fullname := '''';
v_fullaname:= v_first || '' '' || v_last;  RETURN v_fullname;
 
END;'
LANGUAGE 'plpgsql';

then 
type the ff. SELECT stmt:

SELECT empno,get_name(peer),get_name(superior)
FROM table1;

ludwig lim

__________________________________________________
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com

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