From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-sql@postgresql.org
Subject: Re: SQL Statement Help needed
Date: Sun, 4 Dec 2005 14:36:09 +0100
Message-ID: <20051204133609.GA2792@kaufbach.delug.de> (raw)
In-Reply-To: <NBBBLNPHAMCFENDFHIDCEEGHEGAA.Michael.Avila.1@sbcglobal.net>
References: <NBBBLNPHAMCFENDFHIDCEEGHEGAA.Michael.Avila.1@sbcglobal.net>
Michael Avila <Michael.Avila.1@sbcglobal.net> schrieb:
> I am not much of a SQL guru so I am having trouble trying to figure out how
> to format a SQL statement.
>
> I have a table with members named members. Each member has only 1 record.
> Then I have a table with member telephone numbers in it name
> membertelephones. A member can have more than one telephone number (home,
> work, cell, pager, fax, etc.). I want to print out the telephone numbers of
test=# select * from member;
id | name
----+---------
1 | andreas
2 | anja
(2 rows)
test=# select * from member_number ;
id | number
----+--------
1 | 12345
1 | 45678
2 | 232323
(3 rows)
> the members. Is it possible to do it in one SQL statement like with a JOIN
> or something or do I need to get the members and then loop through the
> membertelephones to get the telephone numbers? Is it possible to do a JOIN
> with a table with one record with a table with multiple records?
>
> SELECT * FROM member
>
> SELECT * FROM membertelephone WHERE member_id = the id from the above SELECT
test=# select * from member_number where id = (select id from member
where name = 'andreas');
id | number
----+--------
1 | 12345
1 | 45678
(2 rows)
Btw.: you should use referential integrity between this tables:
t=# \d member_number
Table "public.member_number"
Column | Type | Modifiers
--------+-------------------+-----------
id | integer |
number | character varying |
Foreign-key constraints:
"member_number_id_fkey" FOREIGN KEY (id) REFERENCES member(id)
Btw.: on your mail there was a odd attachment 'winmail.dat'
HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: akretschmer@spamfence.net
Subject: Re: SQL Statement Help needed
In-Reply-To: <20051204133609.GA2792@kaufbach.delug.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox