Re: Joining on a column that might be null

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
>
>