From: Matthew Hagerty <matthew@venux.net>
To: Peter Eisentraut <peter@pathwaynet.com>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Join with blank records.
Date: Wed, 28 Jul 1999 17:46:26 -0400
Message-ID: <4.1.19990728174451.00c33b90@mail.venux.net> (raw)
In-Reply-To: <Pine.LNX.4.10.9907281606140.8870-100000@saxony.pathwaynet. com>
References: <4.1.19990728134746.03f6c4d0@mail.venux.net>
Thank you! That works great, the function that is. The reason the phones
are not in the customer table is because I need to be able to store any
number of phones per customer. All I was trying to do here was get the
first phone number if one exists.
Thanks,
Matthew
At 04:14 PM 7/28/99 -0400, Peter Eisentraut wrote:
>What you're attempting is called a left-join (or right, depending on how
>you look at it), which is not implemented yet.
>
>I had this question a while ago and someone suggested making a function
>like:
>create function phonenr(int4) returns varchar as 'select number from phone
>where cust_id = $1' language 'sql';
>Then you can write
>select cust_id, cust_name, phone_nr(cust_id) from customer;
>which seems to work nicely.
>
>An (infinitely more clumsy) alternative would be
>select customer.cust_id, cust_name, number from customer, phone where
>phone.cust_id = customer.cust_id
>union
>select cust_id, cust_name, NULL from customer where cust_id not in (select
>distinct cust_id from phone);
>
>Then again it would strike me to put both into the same table, since there
>is usually a one to one relationship between a customer and a phone. But
>that's beyond what I can tell from here.
>
>Regards,
>
>Peter
>
>
>On Wed, 28 Jul 1999, Matthew Hagerty wrote:
>
>> Greetings,
>>
>> I have two tables, one is customers and one is their phones. Something
>> simple like this:
>>
>> customer
>> cust_id int4
>> cust_name varchar(30)
>>
>> phone
>> cust_id int4
>> number varchar(15)
>>
>>
>> select c.*, p.number from customer and c, phone as p
>> where c.cust_name='smith'
>> and p.cust_id = c.cust_id;
>>
>> The problem I am having with this is that only records in the customer
>> table that have matching records in the phone number table are showing up.
>> What I would really like is for all records that match the first criteria
>> to show up regardless if they have matching phone number records.
>>
>> Any insight would be greatly appreciated.
>>
>> Thank you,
>> Matthew Hagerty
>>
>>
>
>--
>Peter Eisentraut
>PathWay Computing, Inc.
>
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: matthew@venux.net, peter@pathwaynet.com
Subject: Re: [SQL] Join with blank records.
In-Reply-To: <4.1.19990728174451.00c33b90@mail.venux.net>
References: <4.1.19990728134746.03f6c4d0@mail.venux.net> <4.1.19990728174451.00c33b90@mail.venux.net>
* 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