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

Not sure if I have this the right way round, but one option is a self-join

select p2.first, p2.last, p1.first, p1.last
from people p1, people p2
where p1.person_id = p2.alias
and p2.isalias = true;

Another is to use a sub-select as a column result (haven't checked the
SQL for mistakes)

select p2.first, p2.last, (select first||' - '||last from person p1
where p2.alias = p1.person_id) as realname
from person p2
and p2.isalias = true;

Hope that helps as a starting point.

John

Jim Fitzgerald wrote:
> 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)
>
>
>
>
> ---------------------------(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