Select / sub select? query... help...

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)

Responses

Browse pgsql-general by date

  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?