Join - understand strange results - Article 363 - go-pugleaf

Article View: borland.public.interbase.sql

Article #363

Back to borland.public.interbase.sql Previous Next

Join - understand strange results

#363

From: Adalberto Baldini
Date: Mon, 21 Apr 2008 12:17

30 lines
1994 bytes

I have this example query :
select   a.abinp, a.fil, b.abinp, b.fil, b.ambname, b.filname
from giocoge a
left join  ABFL_USRSOCINP_UNO (:UTENTE, a.csoc, a.nreserc, a.abinp, a.fil) b
on (1=1)
where a.conto = '0601' and a.sezio = '5'
order by a.abinp, a.fil

Result :
ABINP  FIL    ABINP1   FIL1   AMBNAME  FILNAME
-----------------------------------------------
                                XXX     YYY
ABIT   DETO    ABIT    DETO    XX1     YY1

if I change join in :
Select ...
left join  ABFL_USRSOCINP_UNO (:UTENTE, a.csoc, a.nreserc, a.abinp, 
a.fil) b on (a.abinp = b.abinp and a.fil = b.fil  )  ....

I get  :
ABINP  FIL    ABINP1   FIL1   AMBNAME  FILNAME
-----------------------------------------------
                                XXX     YYY
ABIT   DETO    null    null    null    null

Why I loose joined data in 2nd row?
comparison is done on same data passed as parameters.

Thanks
Adalberto Baldini

Technical Details

Message-ID: <480c85b4$1@newsgroups.borland.com>

Path:

arc1-ca.pugleaf.net!g2news1.google.com!news2.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!newsfeed00.sul.t-online.de!newsfeed01.sul.t-online.de!t-online.de!news.isoc.lu!newsgroups.borland.com!not-for-mail

Show Full Headers

Complete Article Headers

X-Google-Thread: 114a0e,11ad783c403646e4,start
X-Google-Attributes: gid114a0e,domainid0,public,usenet
X-Google-Language: ENGLISH,ASCII-7-bit
Date: Mon, 21 Apr 2008 14:17:00 +0200
From: Adalberto Baldini <finman@swissonline.ch>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: borland.public.interbase.sql
Subject: Join - understand strange results
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 080421-0, 21/04/2008), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: 83.77.179.213
Message-ID: <480c85b4$1@newsgroups.borland.com>
X-Trace: newsgroups.borland.com 1208780212 83.77.179.213 (21 Apr 2008 05:16:52 -0700)
Lines: 30
Path: arc1-ca.pugleaf.net!g2news1.google.com!news2.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!newsfeed00.sul.t-online.de!newsfeed01.sul.t-online.de!t-online.de!news.isoc.lu!newsgroups.borland.com!not-for-mail