Outer join problem with 2 different alias for same table.
- From: etienno@xxxxxxxxx
- Date: 25 Jul 2005 08:45:45 -0700
Hi,
I am testing a SQL query on 2 "almost" identical databases. The SQL
query is made by a Java ORM framework (Hibernate), so I dont have too
much control over it. I know the query is very strange because it only
try to make a join between 2 primary key.
The problem is that this query is working fine on the first database
but not on the second database. And the only difference between the 2
DB is that the first database have the declaration of the foreign key
constraints between all tables, and for the second database I didn't
declare the foreign key constraints.
The problem seems to be related to the "left outer join" in the SQL
statement and to the use of 2 different alias for the same database
table. I dont understand why it could work when I declare the
constraints between table and not when I dont declare it. Is that
forbiden to use 2 alias for the same Table?
Here the 2 tables on wich I make the query and the relation between
them:
AstInstrument
| (1..1)
|
|
| (1..1)
AstInstrumentOSS
This is the query wich I have the problem when the foreign keys are not
declare. The problem is very odd, it jam the Sybase database. I need to
restart the Sybase database to close the query. It works well when the
FK are declared.
>>>>>
select AstInstrumentOss1.astInstrumentId as astInstr1_2_,
AstInstrumentOss1.astDataSource as astDataS2_2_2_,
AstInstrument1.astInstrumentId as astInstr1_0_,
AstInstrumentOss2.astInstrumentId as astInstr1_1_,
AstInstrumentOss2.astLastMaintDate as astLast11_2_1_
from dbo.AstInstrumentOss AstInstrumentOss1
left outer join dbo.AstInstrument AstInstrument1 on
AstInstrumentOss1.astInstrumentId=AstInstrument1.astInstrumentId
left outer join dbo.AstInstrumentOss AstInstrumentOss2 on
AstInstrument1.astInstrumentId=AstInstrumentOss2.astInstrumentId
where AstInstrumentOss1.astInstrumentId=15032
>>>>>
Any idea?
Thanks
Etienne
Montreal
.
- Follow-Ups:
- Prev by Date: PROBLEM IN QUEICES THE RS SERVER
- Next by Date: optional parameters
- Previous by thread: PROBLEM IN QUEICES THE RS SERVER
- Next by thread: Re: Outer join problem with 2 different alias for same table.
- Index(es):
Relevant Pages
|
