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

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@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>