From: | "Jim Fitzgerald" <jfitz(at)spacelink(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Select / sub select? query... help... |
Date: | 2006-04-29 19:54:57 |
Message-ID: | e30gad$i7$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello -
I'm trying to figure out how to write a particular query and need some
assistance. I imagine this is extremely simple. I have the table defined
below with five records. This table keeps track of peoples names. Each
person has a unique ID number ("person_id"). The table can also keep track
of alias names for these people. Each record has a flag ("isalias")
indicating whether or not this record indicates a persons real name or a
persons alias name. If it is an alias name then an additional field
("alias") has the number indicating this persons real name record by
person_id (ie alias field of an alias record == the person_id of the real
name record).
I want a query that will select all entries where "isalias" is true and
will display the person_id, first, and last fields from the alias record and
ALSO the first and last fields from the real name entry.
Output would be something like this for the example data below
3 - Johns - Alias - John - Smith
4 - Marks - Alias - Mark - Twain
Any thoughts on how this can be accomplished easily / efficiently?
Thanks
-Jim
Table "public.people"
Column | Type | Modifiers
-----------+-----------------------+-----------
person_id | integer |
first | character varying(20) |
last | character varying(20) |
alias | integer |
isalias | boolean |
Containing the example data:
person_id | first | last | alias | isalias
-----------+-------+-------+-------+---------
1 | John | Smith | 0 | f
2 | Mark | Twain | 0 | f
3 | Johns | Alias | 1 | t
4 | Marks| Alias | 2 | t
5 | someone | else | 0| f
(5 rows)
From | Date | Subject | |
---|---|---|---|
Next Message | John Sidney-Woollett | 2006-04-29 20:39:43 | Re: Select / sub select? query... help... |
Previous Message | Dan Black | 2006-04-29 19:49:00 | how can i view deleted records? |