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(at)pobox(dot)com>
To: <pgsql-novice(at)postgresql(dot)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(at)pobox(dot)com
>
>