Jim Fitzgerald wrote:
Hello -
Probably would be better to ask, "how do I store this data?"
Then the query writes itself. Put people in one table, put aliases in
another.
CREATE TABLE people (
person_id int
,first varchar(20)
,last varchar(20)
)
CREATE TABLE aliases (
person_id int references people (person_id)
,first varchar(20)
,last varchar(20)
)
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)
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
begin:vcard
fn:Kenneth Downs
n:Downs;Kenneth
email;internet:ken@xxxxxxxxxx
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard
