At 04:12 AM 12/4/05, Michael Avila wrote:
>I have a table with members named members. Each member has only 1 record.
>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

Yes.

>do I need to get the members and then loop through the
>membertelephones to get the telephone numbers?

No.

>Is it possible to do a JOIN
>with a table with one record with a table with multiple records?

Yes.


>SELECT * FROM member
>
>SELECT * FROM membertelephone WHERE member_id = the id from the above SELECT

http://en.wikipedia.org/wiki/Join_%28SQL%29


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

to the OP (i couldn't find it, so i'm replying to this
note),

have you thought about normalizing your data structure
a bit further?

your table structure currently appears to be:

table_employees
employee_id
employee_name
home_phone
work_phone
cell_phone
pager
fax
etc...

you could set it up as follows...

table_employees
employee_id
employee_name

table_phone_number
phone_number_id
phone_number
type_id

table_type_phone_number (note: type_name is where
"home", "work", "fax", etc. gets entered)
type_id
type_name

table_link_employee_phone_number
employee_id
phone_number_id

the reason for doing this is that you eliminate
database dead space (people that don't have faxes
won't store a null in the db (i think it is null) and
the db won't have to manage the null values).

best of luck.



--- Frank Bax <fbax@sympatico.ca> wrote:

> At 04:12 AM 12/4/05, Michael Avila wrote:
> >I have a table with members named members. Each
> member has only 1 record.
> >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
>
> Yes.
>
> >do I need to get the members and then loop through
> the
> >membertelephones to get the telephone numbers?
>
> No.
>
> >Is it possible to do a JOIN
> >with a table with one record with a table with
> multiple records?
>
> Yes.
>
>
> >SELECT * FROM member
> >
> >SELECT * FROM membertelephone WHERE member_id = the
> id from the above SELECT
>
> http://en.wikipedia.org/wiki/Join_%28SQL%29
>
>
>
> ---------------------------(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
>




__________________________________________
Yahoo! DSL – Something to write home about.
Just $16.99/mo. or less.
dsl.yahoo.com


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

From: <operationsengineer1@yahoo.com>
> the reason for doing this is that you eliminate
> database dead space (people that don't have faxes
> won't store a null in the db (i think it is null) and
> the db won't have to manage the null values).

correct me if i'm wrong, but i don't think having a completely normalized
datastructure makes always sense. if one would need to query an employee and
having all phone numbers & stuff in one single view, you'll end up having a
huge amount of joins. this is bound to "cost" performance while the "win"
(diskspace) is relatively small.

- thomas



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--- me@alternize.com wrote:

> From: <operationsengineer1@yahoo.com>
> > the reason for doing this is that you eliminate
> > database dead space (people that don't have faxes
> > won't store a null in the db (i think it is null)
> and
> > the db won't have to manage the null values).
>
> correct me if i'm wrong, but i don't think having a
> completely normalized
> datastructure makes always sense. if one would need
> to query an employee and
> having all phone numbers & stuff in one single view,
> you'll end up having a
> huge amount of joins. this is bound to "cost"
> performance while the "win"
> (diskspace) is relatively small.
>
> - thomas

good point. this is absolutely true...

the OP has enough information to make the best choice
for their situation.

sometimes normalization makes sense, other times it
may not.



__________________________________________
Yahoo! DSL – Something to write home about.
Just $16.99/mo. or less.
dsl.yahoo.com


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On 12/5/05, me@alternize.com <me@alternize.com> wrote:
> From: <operationsengineer1@yahoo.com>
> > the reason for doing this is that you eliminate
> > database dead space (people that don't have faxes
> > won't store a null in the db (i think it is null) and
> > the db won't have to manage the null values).
>
> correct me if i'm wrong, but i don't think having a completely normalized
> datastructure makes always sense. if one would need to query an employee and
> having all phone numbers & stuff in one single view, you'll end up having a
> huge amount of joins. this is bound to "cost" performance while the "win"
> (diskspace) is relatively small.
>
> - thomas
>

there are cases, but you still want to justify yourself the use of
denormalized data...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Dec 5, 2005, at 10:44 PM, <me@alternize.com> <me@alternize.com>
wrote:

> correct me if i'm wrong, but i don't think having a completely
> normalized datastructure makes always sense. if one would need to
> query an employee and having all phone numbers & stuff in one
> single view, you'll end up having a huge amount of joins. this is
> bound to "cost" performance while the "win" (diskspace) is
> relatively small.

Actually, it depends on what you're after in the long run. De-
normalizing phone numbers might make sense if all you really need is
the current contact information for a particular user. But if you
really need a history of sorts of the contact information associated
with a user, normalization makes all the sense in the world. As
well, you can't really predict how your information needs will
evolve. You can make educated guesses but you really can't say,
"We'll never need that." As always, make a decision based on your
particular situation and what your needs are. It also doesn't hurt
to plan a little for things that may never happen but could. The
more you do so, the less traumatic it will be to change later.

Charley

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match