pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

SQL Statement Help needed
2+ messages / 2 participants
[nested] [flat]

* SQL Statement Help needed
@ 2005-12-04 12:31  Michael Avila <Michael.Avila.1@sbcglobal.net>
  0 siblings, 1 reply; 2+ messages in thread

From: Michael Avila @ 2005-12-04 12:31 UTC (permalink / raw)
  To: pgsql-sql

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

Thanks for the help.

Mike

Attachments:

  winmail.dat (application/ms-tnef, 1.7K) download

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: SQL Statement Help needed
@ 2005-12-04 13:36  Andreas Kretschmer <akretschmer@spamfence.net>
  parent: Michael Avila <Michael.Avila.1@sbcglobal.net>
  0 siblings, 0 replies; 2+ messages in thread

From: Andreas Kretschmer @ 2005-12-04 13:36 UTC (permalink / raw)
  To: pgsql-sql

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°



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2005-12-04 13:36 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-04 12:31 SQL Statement Help needed Michael Avila <Michael.Avila.1@sbcglobal.net>
2005-12-04 13:36 ` Andreas Kretschmer <akretschmer@spamfence.net>

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