Re: [SQL] Join with blank records.

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