From: | Peter Eisentraut <peter(at)pathwaynet(dot)com> |
---|---|
To: | Matthew Hagerty <matthew(at)venux(dot)net> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: [SQL] Join with blank records. |
Date: | 1999-07-28 20:14:32 |
Message-ID: | Pine.LNX.4.10.9907281606140.8870-100000@saxony.pathwaynet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Matthew Hagerty | 1999-07-28 21:46:26 | Re: [SQL] Join with blank records. |
Previous Message | Matthew Hagerty | 1999-07-28 18:05:34 | Join with blank records. |