RE: [Info-ingres] Outer join problem
- From: "Peter Gale" <peter.gale@xxxxxxxxxx>
- Date: Mon, 6 Mar 2006 12:48:45 -0000
Of course you may well ask why it is necessary to ifnull(t3.acct,''). I may
be wrong here but because there is no actual join between t1 and t3 this
results is a cart prod left join between t1 and t3. t3.acct is being used
after that outer join, hence it is null.
--
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 Peter Gale
Sent: 06 March 2006 12:31
To: Piotr.Wisniewski@xxxxxxxxxxxx; info-ingres@xxxxxxxxxxxxxxx
Subject: RE: [Info-ingres] Outer join problem
Because that first comparison t1.id=t2.bank_id is not comparing NULLS. It
compares the rows in t1 with t2. There are no rows in t2 so the outer join
returns nulls for the t2 columns which are then used in the outer join for
t3.
--
Peter
T: +44 (0)1398 341777
PGale@xxxxxxxxxxxxxxx
-----Original Message-----
From: Piotr.Wisniewski@xxxxxxxxxxxx [mailto:Piotr.Wisniewski@xxxxxxxxxxxx]
Sent: 06 March 2006 12:25
To: PGale@xxxxxxxxxxxxxxx; info-ingres@xxxxxxxxxxxxxxx
Subject: RE: [Info-ingres] Outer join problem
Ok.,
So why does
select t1.*
from tmp1 t1
left join tmp2 t2 on t1.id=t2.bank_id
return 2 rows ?
-----Original Message-----
From: Peter Gale [mailto:PGale@xxxxxxxxxxxxxxx]
Sent: Monday, March 06, 2006 1:14 PM
To: Piotr.Wisniewski@xxxxxxxxxxxx; info-ingres@xxxxxxxxxxxxxxx
Subject: 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
_______________________________________________
Info-ingres mailing list
Info-ingres@xxxxxxxxxxxxxxx
http://mailman.cariboulake.com/mailman/listinfo.py/info-ingres
.
- Prev by Date: Re: [Info-ingres] Outer join problem
- Next by Date: Re: [Info-ingres] Outer join problem
- Previous by thread: RE: [Info-ingres] Outer join problem
- Next by thread: Re: [Info-ingres] Outer join problem
- Index(es):
Relevant Pages
|
