Thread: Joining on a column that might be null

Joining on a column that might be null

From
"James McMurry"
Date:
I'm afraid this will expose me as a true SQL novice.  But this is the place
for that, so here goes...

Here's a simplified version of what I want to do.  Anyone know how?  Or can
anyone just tell me with authority, "you can't do that!"

I have two tables: "item" and "member".

"item" has these columns:
item_id integer
item_name varchar(100)
created_by integer

"member" has these columns:
member_id integer
member_name varchar(100)


item.created_by contains entries from member.member_id, and I'd like to
select records from item with the full member_name:

select a.item_id, a.item_name, b.member_name
from item a, member b
where a.created_by = b.member_id


The problem I'm having is that created_by can be null.  The above statement
won't give me results for those records.  I tried changing the where
statement to:

where a.created_by = b.member_id or a.created_by is null


Of course, this gives me every record in "member" for every record in "item"
that has a null "created_by" value.

I've been goofing around with all sorts of things (case, union, subselects)
trying to get it to work, but everything I've tried has given me one of the
two above results.

I'm not far from giving up and working around it in my code, but before I
do, I thought I'd share my ignorance and see if anyone can help out.

Thanks!
jmcmurry@pobox.com


Re: Joining on a column that might be null

From
"James McMurry"
Date:
Oops, should have pointed out that because this is a simplified version,
I've removed from the select clause some other result columns I want from
the "item" table, as well as from some other tables.  Obviously a join on
these two tables is pointless if there's no common record between them (one
is null), and in the interest of being simple, I made it stupid.

To the table info below, I should have added a "info" column to "item".  If
created_by is null, info won't be null, and vice versa.

And in the select, I want to get the item_id, item_name, and either the
member_name (using created_by) or the info column for each record in "info".

Maybe I should re-read my posts when I've been slaving away with no success
for two hours.  Ugh.

JM

----- Original Message -----
From: "James McMurry" <jmcmurry@pobox.com>
To: <pgsql-novice@postgresql.org>
Sent: Tuesday, February 27, 2001 6:14 PM
Subject: [NOVICE] Joining on a column that might be null


> I'm afraid this will expose me as a true SQL novice.  But this is the
place
> for that, so here goes...
>
> Here's a simplified version of what I want to do.  Anyone know how?  Or
can
> anyone just tell me with authority, "you can't do that!"
>
> I have two tables: "item" and "member".
>
> "item" has these columns:
> item_id integer
> item_name varchar(100)
> created_by integer
>
> "member" has these columns:
> member_id integer
> member_name varchar(100)
>
>
> item.created_by contains entries from member.member_id, and I'd like to
> select records from item with the full member_name:
>
> select a.item_id, a.item_name, b.member_name
> from item a, member b
> where a.created_by = b.member_id
>
>
> The problem I'm having is that created_by can be null.  The above
statement
> won't give me results for those records.  I tried changing the where
> statement to:
>
> where a.created_by = b.member_id or a.created_by is null
>
>
> Of course, this gives me every record in "member" for every record in
"item"
> that has a null "created_by" value.
>
> I've been goofing around with all sorts of things (case, union,
subselects)
> trying to get it to work, but everything I've tried has given me one of
the
> two above results.
>
> I'm not far from giving up and working around it in my code, but before I
> do, I thought I'd share my ignorance and see if anyone can help out.
>
> Thanks!
> jmcmurry@pobox.com
>
>


Re: Joining on a column that might be null

From
Tom Lane
Date:
"James McMurry" <jmcmurry@pobox.com> writes:
> item.created_by contains entries from member.member_id, and I'd like to
> select records from item with the full member_name:
> select a.item_id, a.item_name, b.member_name
> from item a, member b
> where a.created_by = b.member_id
> The problem I'm having is that created_by can be null.  The above statement
> won't give me results for those records.

I believe what you're looking for is an "outer join".   We have this for
real in PG 7.1, but in earlier releases you have to fake it with a UNION
construct.  See http://www.postgresql.org/docs/faq-english.html#4.24

            regards, tom lane