Joining on a column that might be null

From: "James McMurry" <jmcmurry(at)pobox(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Joining on a column that might be null
Date: 2001-02-28 00:14:31
Message-ID: 00b401c0a11b$75e11c90$05000100@dorkboxw2k
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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(at)pobox(dot)com

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message James McMurry 2001-02-28 00:27:28 Re: Joining on a column that might be null
Previous Message Heath Johns 2001-02-27 23:43:55 Re: Permissions