Joining on a column that might be null
От | James McMurry |
---|---|
Тема | Joining on a column that might be null |
Дата | |
Msg-id | 00b401c0a11b$75e11c90$05000100@dorkboxw2k обсуждение исходный текст |
Ответы |
Re: Joining on a column that might be null
|
Список | 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@pobox.com
В списке pgsql-novice по дате отправления: