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

Thanks for the advice.. I got it working!

-Jim

"Jim Fitzgerald" <jfitz(at)spacelink(dot)com> wrote in message
news:e30gad$i7$1(at)news(dot)hub(dot)org(dot)(dot)(dot)
> 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)
>
>
>