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)
