Re: [SQL] Join with blank records.

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.