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