Path: news.f.de.plusline.net!news-fra1.dfn.de!news-lei1.dfn.de!newsfeed00.sul.t-online.de!t-online.de!novia!news-out.readnews.com!transit4.readnews.com!postnews.google.com!y66g2000hsf.googlegroups.com!not-for-mail
From: "Steve" <stevelaba@yahoo.com>
Newsgroups: comp.databases.oracle.misc
Subject: Outer Join issue
Date: 4 Apr 2007 12:21:39 -0700
Organization: http://groups.google.com
Lines: 29
Message-ID: <1175714499.874084.288020@y66g2000hsf.googlegroups.com>
NNTP-Posting-Host: 165.89.84.88
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1175714505 1373 127.0.0.1 (4 Apr 2007 19:21:45 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 4 Apr 2007 19:21:45 +0000 (UTC)
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: y66g2000hsf.googlegroups.com; posting-host=165.89.84.88;
   posting-account=IIC4vw0AAACsTdJz2iBDlaIoSUbeIRSj
Xref: news.f.de.plusline.net comp.databases.oracle.misc:78654

Hello all, using 9.2 and can't understand why my join is not working.
When I outer join table addr using mn_sale.contracted_cust_id  =
addr.member_id(+)  I get back less rows then if I don't join this
table.

I believe I should be getting the same amount of rows that the table
mn_sale returns.

Please advise.

Thanks

Steve

SELECT count(1)
 FROM modn.mn_sale mn_sale ,
       modn.mn_address addr ,
       modn.mn_date_dim dt_dim
  WHERE  mn_sale.realm_num       = 1024
     AND     mn_sale.lifecycle_status = 30
     AND     addr.addr_flags                =  1
     AND to_date(dt_dim.short_date_ymd,'YYYY-MM-DD')
    BETWEEN to_date('2006-07-01','YYYY-MM-DD')
     AND           to_date('2006-12-31','YYYY-MM-DD')
     AND mn_sale.sale_type                 <>  1
     AND mn_sale.inv_date_id               = dt_dim.date_dim_id
     AND mn_sale.contracted_cust_id  = addr.member_id(+)
    AND addr.realm_num                      = 1024