pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: simple select statement inquiry
3+ messages / 3 participants
[nested] [flat]
* Re: simple select statement inquiry
@ 2002-06-06 03:33 Ludwig Lim <lud_nowhere_man@yahoo.com>
parent: Joseph Syjuco <joseph@asti.dost.gov.ph>
0 siblings, 1 reply; 3+ messages in thread
From: Ludwig Lim @ 2002-06-06 03:33 UTC (permalink / raw)
To: Joseph Syjuco <joseph@asti.dost.gov.ph>; pgsql-sql
--- 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* simple select statement inquiry
@ 2002-06-06 14:59 Joseph Syjuco <joseph@asti.dost.gov.ph>
0 siblings, 1 reply; 3+ messages in thread
From: Joseph Syjuco @ 2002-06-06 14:59 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: simple select statement inquiry
@ 2002-06-06 16:49 Joel Burton <joel@joelburton.com>
parent: Ludwig Lim <lud_nowhere_man@yahoo.com>
0 siblings, 0 replies; 3+ messages in thread
From: Joel Burton @ 2002-06-06 16:49 UTC (permalink / raw)
To: Ludwig Lim <lud_nowhere_man@yahoo.com>; +Cc: Joseph Syjuco <joseph@asti.dost.gov.ph>; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-06-06 16:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-06 14:59 simple select statement inquiry Joseph Syjuco <joseph@asti.dost.gov.ph>
2002-06-06 03:33 ` Ludwig Lim <lud_nowhere_man@yahoo.com>
2002-06-06 16:49 ` Joel Burton <joel@joelburton.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox