=?ISO-8859-1?Q?RE:_Problems_with_an_outer_join._It_joins_a_second_table_even_it_ should_not,_there_is_no_records_in_a_second_for_specified_criteria.?=

Why join of A and B is performed at all if there is no matching record bet= ween=20 B and C, this record from B table should be dropped because subset of B and= C=20 returns nothing for this row=2E My point is why B and C are not joined firs= t=20 (there should be inner join between them) and then base on this result set=20= join=20 with A should be performed=2E Thanks for your response=2E =20 Todd Bernhardt <TBernhardt@wii=2Ecom> on 10/09/98 03:47:38 PM To: Rafal Furdzik/PaulWeiss cc: =20 Subject: RE: Problems with an outer join=2E It joins a second table even it= =20 should not, there is no records in a second for specified criteria=2E This looks correct to me: The join between B and C will return only the FK2 =3D "A" data set--because of your C=2Epk in ('A') criteria=2E When you join A with the outer B and C, you will get the A=2EFK2 =3D "B" record returned with a null C=2EPK because there isn't a matching C record=2E Hope this makes sense=2E I don't have time at the moment to rework the wording=2E -Todd > ---------- > From: rfurdzik@paulweiss=2Ecom[SMTP:rfurdzik@paulweiss=2Ecom] > Sent: Thursday, October 08, 1998 1:36 PM > To: informix-list@iiug=2Eorg > Subject: Problems with an outer join=2E It joins a second table > even it should not, there is no records in a second for specified > criteria=2E > > When you do an outer join with another table to which you applied > criteria > which in result returns no data, an outer join still will be performed > joining > a row from first table with an empty row=2E Please help=2E > > > drop table tempa; > drop table tempc; > create table tempA(PK Integer, FK INTEGER, FK2 Char(1)); > create table tempC(PK Char(1)); > INSERT INTO tempA VALUES (10, null, 'A'); > INSERT INTO tempA VALUES (20, 10, 'B'); > INSERT INTO tempA VALUES (30, 40, 'A'); > INSERT INTO tempC VALUES ('A'); > INSERT INTO tempC VALUES ('B');> > select A=2EPK, A=2EFK, B=2EPK, B=2EFK2, C=2EPK > FROM tempA As A, OUTER (tempA As B, tempC As C) > Where A=2EFK=3DB=2EPK > AND B=2EFK2=3DC=2EPK > AND C=2Epk is not null > AND C=2Epk In('A') > > It is showing rows where C=2Epk is null an is not in 'A'=2E Is it a bug > ???? Please > help > > > > __________________________________________________ > > This message is intended only for the use of the > Addressee and may contain information that is > PRIVILEGED and CONFIDENTIAL=2E If you are not the > intended recipient, you are hereby notified that > any dissemination of this communication is > strictly prohibited=2E If you have received this > communication in error, please erase all copies of > the message and its attachments and notify us > immediately=2E Thank You=2E > __________________________________________________ > > > > > > > > __________________________________________________ > > This message is intended only for the use of the > Addressee and may contain information that is > PRIVILEGED and CONFIDENTIAL=2E If you are not the > intended recipient, you are hereby notified that > any dissemination of this communication is > strictly prohibited=2E If you have received this > communication in error, please erase all copies of > the message and its attachments and notify us > immediately=2E Thank You=2E > __________________________________________________ >=20 __________________________________________________ This message is intended only for the use of the=20 Addressee and may contain information that is=20 PRIVILEGED and CONFIDENTIAL=2E If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited=2E If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately=2E Thank You=2E __________________________________________________