Обсуждение: Select / sub select? query... help...
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)
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
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 > >
Вложения
Thanks for the advice.. I got it working! -Jim "Jim Fitzgerald" <jfitz@spacelink.com> wrote in message news:e30gad$i7$1@news.hub.org... > 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) > > >