Thread: Join with blank records.

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

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.

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