Thread: 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
Attachment
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