Path: text.usenetserver.com!out02b.usenetserver.com!news.usenetserver.com!in04.usenetserver.com!news.usenetserver.com!fu-berlin.de!newsoffice.de!not-for-mail
From: Andreas Mosmann <mosmann@expires-31-03-2007.news-group.org>
Newsgroups: comp.databases.oracle.misc
Subject: Re: empty string or null
Date: Fri, 30 Mar 2007 09:25:28 +0200
Organization: Newsoffice.de - http://www.newsoffice.de
Lines: 48
Sender: Andreas Mosmann <mosmann@expires-31-03-2007.news-group.org>
Message-ID: <1175239528.67@user.newsoffice.de>
References: <ohQOh.86220$DN.61963@pd7urf2no> <1175179877.256006.199430@n59g2000hsh.googlegroups.com> <1175182587.54@user.newsoffice.de> <euh3bm$rhd$1@news4.zwoll1.ov.home.nl>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
X-Trace: localhost.localdomain 1175239528 8918 127.0.0.1 (30 Mar 2007 07:25:28 GMT)
X-Complaints-To: usenet@localhost.localdomain
NNTP-Posting-Date: Fri, 30 Mar 2007 07:25:28 +0000 (UTC)
User-Agent: Newsoffice.de - based on NewsPortal
X-Abuse-Management: 0sLLl6HbyJjax8TM5s3VmM+ZVXepmWNVY2FVsM6jQGeaYm1VaGpaoKyrUmxVY2BrmqY=
Xref: usenetserver.com comp.databases.oracle.misc:246711
X-Received-Date: Fri, 30 Mar 2007 02:25:30 EST (text.usenetserver.com)

Frank van Bortel schrieb am 29.03.2007 in 
<euh3bm$rhd$1@news4.zwoll1.ov.home.nl>:

> Andreas Mosmann schreef:
> [snip]
>> try a JOIN ...
>> SELECT * FROM ADDRESSES A
>> LEFT JOIN JOBS J ON
>>  (A.FIRSTNAME=B.FIRSTNAME) and (A.MIDDLENAME=B.MIDDLENAME) and
>>  (A.FAMILYNAME=B.FAMILIYNAME);
>> 
>> no MIDDLENAME- no job
>> 
>> Andreas Mosmann
>> 
>> 
> Does this mean I have a job, and you don't?
> :)

I know about VARCHAR2, so I would write the correct query ;)

SELECT * FROM ADDRESSES A
LEFT JOIN JOBS J ON
 (A.FIRSTNAME=B.FIRSTNAME
   or A.FIRSTNAME is NULL and B.FIRSTNAME is NULL)
 and
 (A.MIDDLENAME=B.MIDDLENAME
   or A.MIDDLENAME is NULL and B.MIDDLENAME is NULL)
 and
 (A.FIRSTNAME=B.FAMILIYNAME
   or A.FAMILIYNAME is NULL and B.FAMILIYNAME is NULL)
 and NULL is NULL
 and 1=1
 and 2=2
 ..
 and 'A'='A'
 ..

are you interested in knowing which job has Mr.  ?
(long time ago he had to call himself "nobody", but now "" is a correct 
name)


best regards
Andreas <no middlename> Mosmann

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de