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

Started by Jim Fitzgeraldalmost 20 years ago4 messagesgeneral

jfitz@spacelink.com

almost 20 years ago

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

Containing the example data:

(5 rows)

John Sidney-Woollett

johnsw@wardbrook.com

almost 20 years ago

In reply to: Jim Fitzgerald (#1)

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:

Show quoted text

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

Kenneth Downs

ken@secdat.com

almost 20 years ago

In reply to: Jim Fitzgerald (#1)

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)
)

Show quoted text

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

jfitz@spacelink.com

almost 20 years ago

In reply to: Jim Fitzgerald (#1)

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

Thanks for the advice.. I got it working!

-Jim

"Jim Fitzgerald" <jfitz@spacelink.com> wrote in message
news:e30gad$i7$1@news.hub.org...

Show quoted text

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)