pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedJoin with blank records.
3+ messages / 2 participants
[nested] [flat]
* Join with blank records.
@ 1999-07-28 18:05 Matthew Hagerty <matthew@venux.net>
0 siblings, 2 replies; 3+ messages in thread
From: Matthew Hagerty @ 1999-07-28 18:05 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] Join with blank records.
@ 1999-07-28 20:14 Peter Eisentraut <peter@pathwaynet.com>
parent: Matthew Hagerty <matthew@venux.net>
1 sibling, 0 replies; 3+ messages in thread
From: Peter Eisentraut @ 1999-07-28 20:14 UTC (permalink / raw)
To: Matthew Hagerty <matthew@venux.net>; +Cc: 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] Join with blank records.
@ 1999-07-28 21:46 Matthew Hagerty <matthew@venux.net>
parent: Matthew Hagerty <matthew@venux.net>
1 sibling, 0 replies; 3+ messages in thread
From: Matthew Hagerty @ 1999-07-28 21:46 UTC (permalink / raw)
To: Peter Eisentraut <peter@pathwaynet.com>; +Cc: pgsql-sql
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.
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1999-07-28 21:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-28 18:05 Join with blank records. Matthew Hagerty <matthew@venux.net>
1999-07-28 20:14 ` Peter Eisentraut <peter@pathwaynet.com>
1999-07-28 21:46 ` Matthew Hagerty <matthew@venux.net>
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