RE: [Info-ingres] Outer join problem

Hi Piotr,

NULLS!!!!!

As you are outer joining an outer join there is the potential for NULLS
being joined to NULLS, the result of which is completely undefined.

t2.bank_nr will be null in the join as will t3.acct. Wrap them up in
suitable IFNULL statements and the desired result is obtained.

select t1.*
from tmp1 t1
left join tmp2 t2 on t1.id=t2.bank_id
left join tmp3 t3 on
t1.acct=varchar(ifnull(t2.bank_nr,''))+ifnull(t3.acct,'')



--
Peter Gale
Comprehensive Solutions International
T: +44 (0)1398 341777 M: +44 (0)7831 513181
PGale@xxxxxxxxxxxxxxx www.Comp-Soln.co.uk

-----Original Message-----
From: info-ingres-admin@xxxxxxxxxxxxxxx
[mailto:info-ingres-admin@xxxxxxxxxxxxxxx] On Behalf Of
Piotr.Wisniewski@xxxxxxxxxxxx
Sent: 06 March 2006 12:00
To: info-ingres@xxxxxxxxxxxxxxx
Subject: [Info-ingres] Outer join problem

In my opinion the following SELECT should return 2 rows.
It returns 0 rows on ingres 2.6 and r3.

The SQL docs say: outer joins are evaluated left to right by default.
If table tmp1 is non empty then the SELECT of the form
SELECT ........
FROM tmp1
LEFT JOIN tmp2 ON cond1
LEFT JOIN tmp3 ON cond2

should be nonzero no matter what the conditions cond1, cond2 are.

Am I right or not ?

Piotr


-----------------------------------------------------
create table tmp1 (id i1, acct varchar(8) );
insert into tmp1 values (1, '');
insert into tmp1 values (2, '');

create table tmp2 (bank_id i4, bank_nr varchar(8) );
create table tmp3 (nl_id i4, acct varchar(8) );

select t1.*
from tmp1 t1
left join tmp2 t2 on t1.id=t2.bank_id
left join tmp3 t3 on t1.acct=varchar(bank_nr)+t3.acct
;
rollback\g
_______________________________________________
Info-ingres mailing list
Info-ingres@xxxxxxxxxxxxxxx
http://mailman.cariboulake.com/mailman/listinfo.py/info-ingres

.

Relevant Pages

  • Re: Space required for an empty varchar field?
    ... >who don't understand how to deal with NULLS. ... as they'll be in the result set of an outer join. ... it gets unwieldy fast if you have multiplle NULLable columns. ... Thanks Hugo. ...
    (microsoft.public.sqlserver.programming)
  • RE: [Info-ingres] Outer join problem
    ... Subject: Outer join problem ... If table tmp1 is non empty then the SELECT of the form ... LEFT JOIN tmp3 ON cond2 ... should be nonzero no matter what the conditions cond1, cond2 are. ...
    (comp.databases.ingres)
  • Re: Second opinion for a query
    ... LEFT OUTER JOIN dbo.tblImage ... You should judge by yourself if you think it's better. ... back to this query in a year or so from now. ... There are a lot of people claiming that NULLs should be avoided, ...
    (microsoft.public.sqlserver.programming)
  • RE: [Info-ingres] Outer join problem
    ... Subject: [Info-ingres] Outer join problem ... Because that first comparison t1.id=t2.bank_id is not comparing NULLS. ...
    (comp.databases.ingres)
  • RE: [Info-ingres] Outer join problem
    ... Because that first comparison t1.id=t2.bank_id is not comparing NULLS. ... There are no rows in t2 so the outer join ... Subject: [Info-ingres] Outer join problem ... If table tmp1 is non empty then the SELECT of the form ...
    (comp.databases.ingres)