Обсуждение: SQL Statement Help Needed

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

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

Michael Avila wrote:

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

Yes.  Do you have postgreSQL running somewhere, available to play with?

The best way to learn is by doing, then reading, then doing some more.
Find a begining tutorial and get started.

brew

Thank you everyone.

A received a few responses and they all work. However, with one member name
but multiple telephone numbers, is there a way to not get the multiple
member names, the same name with each telephone? Or do I have to "filter"
that out myself?

Thanks

Mike

-----Original Message-----
From: Oliver Elphick [mailto:olly@lfix.co.uk]
Sent: Sunday, December 04, 2005 11:40 AM
To: Michael Avila
Subject: Re: [NOVICE] SQL Statement Help Needed


On Sun, 2005-12-04 at 04:12 -0500, Michael Avila wrote:
> ... 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

The query would be:

   SELECT   *
     FROM   member AS m
            LEFT JOIN membertelephone as t
              ON m.id = t.member_id;

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html

Hi,

That would be the same trick as was shown to Srinivas Iyyer the other day:

select
    m.membername
    array (
        select t.phonenumber
        from membertelephone t
        where t.member_id = m.id
    )
from
    member m

"Normally", I guess, people would filter.
Note that in this select you get a row for all members, if you do a join
you won't get a row for those members without a phone (unless you
specifically make it so, Oracle's (+) syntax, I don't know what that is
in pg?!)

Best regards,
Marcus

Michael Avila wrote:
> Thank you everyone.
>
> A received a few responses and they all work. However, with one member name
> but multiple telephone numbers, is there a way to not get the multiple
> member names, the same name with each telephone? Or do I have to "filter"
> that out myself?
>
> Thanks
>
> Mike
>
> -----Original Message-----
> From: Oliver Elphick
> Sent: Sunday, December 04, 2005 11:40 AM
> To: Michael Avila
> Subject: Re: [NOVICE] SQL Statement Help Needed
>
>
> On Sun, 2005-12-04 at 04:12 -0500, Michael Avila wrote:
>
>>... 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
>
> The query would be:
>
>    SELECT   *
>      FROM   member AS m
>             LEFT JOIN membertelephone as t
>               ON m.id = t.member_id;
>

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

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

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

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

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