Thread: 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