I hope someone can help me on this problem.
I want a join between 3 tables (TWS060, TWS040 and TWS066) where I need an inner join between tws060 and tws040 and a left outer join between tws060 and tws066.
As you can see in the sql-query below, Hibernate makes two joins between tws060 and tws066, once an inner join and once the (correct) left outer join. I don't need that inner join between tws060 and tws066.
How can I remove this join?
[b]Hibernate version:[/b]
Hibernate 3.0.5
[b]Mapping documents:[/b]
<class
name="com.volvocars.vcc.cws.data.AtacqItemOnCar"
table="TWS060"
>
<id name="id" column="NIDTWS060" type="java.lang.Integer"
unsaved-value="null"
>
</id>
<property
name="missingPartStatusId"
type="java.lang.Integer"
column="NIDTWS066"
not-null="false"
unique="false"
/>
<many-to-one
name="atacqItem"
class="com.volvocars.vcc.cws.data.AtacqItem"
cascade="none"
outer-join="auto"
column="nidtws040"
not-null="true"
/>
<property
name="atacqItemId"
type="java.lang.Integer"
column="NIDTWS040"
not-null="false"
unique="false"
/>
<many-to-one
name="missingPartStatus"
class="com.volvocars.vcc.cws.data.MissingPartStatus"
column="nidtws066"
not-null="false"
/>
</class>
<class
name="com.volvocars.vcc.cws.data.MissingPartStatus"
table="TWS066"
>
<id
name="id"
column="NIDTWS066"
type="java.lang.Integer"
unsaved-value="null"
>
</id>
<set
name="atacqItemOnCar" fetch="join"
>
<key
column="nidtws066"
>
</key>
<one-to-many
class="com.volvocars.vcc.cws.data.AtacqItemOnCar"
/>
</set>
</class>
<class
name="com.volvocars.vcc.cws.data.AtacqItem"
table="TWS040"
>
<id
name="id"
column="NIDTWS040"
type="java.lang.Integer"
unsaved-value="null"
>
</id>
<set
role="atacqItemOnCar"
table="AtacqItemOnCar"
lazy="false"
readonly="false"
inverse="true"
cascade="none"
sort="unsorted"
>
<key
column="nidtws040"
>
</key>
<one-to-many
class="com.volvocars.vcc.cws.data.AtacqItemOnCar"
/>
</set>
</class>
[b]Name and version of the database you are using:[/b]
Oracle8
[b]The generated SQL (show_sql=true):[/b]
Hibernate: select this_.NIDTWS060 as NIDTWS1_3_, this_.IBODY as IBODY1_3_, this_.NIDTWS066 as NIDTWS3_1_3_, this_.nidtws040 as nidtws4_1_3_, this_.NIDTWS040 as NIDTWS5_1_3_, this_.nidtws066 as nidtws6_1_3_, this_.ICHASSIS as ICHASSIS1_3_, this_.IFYON as IFYON1_3_, this_.DBMELD as DBMELD1_3_, this_.IPERSMEL as IPERSMEL1_3_, this_.RHFDTYP as RHFDTYP1_3_, this_.IVMIX as IVMIX1_3_, this_.RPRODLYN as RPRODLYN1_3_, this_.NIDTWS040_2 as NIDTWS14_1_3_, this_.DBAFMELD as DBAFMELD1_3_, this_.IPERSAFMELD as IPERSAF16_1_3_, this_.IPLAFMELD as IPLAFMELD1_3_, this_.IWRKPOSTAFM as IWRKPOS18_1_3_, this_.DBDATST as DBDATST1_3_, this_.NIDTWS300 as NIDTWS20_1_3_, this_.TMWRKFT as TMWRKFT1_3_, ai1_.NIDTWS040 as NIDTWS1_0_, ai1_.IATACQ as IATACQ0_0_, ai1_.OATACQ as OATACQ0_0_, ai1_.FMELD as FMELD0_0_, ai1_.FAFMELD as FAFMELD0_0_, ai1_.BLOKKERENDFLAG as BLOKKERE6_0_0_, ai1_.NIDTWS030 as NIDTWS7_0_0_, ai1_.NKOST as NKOST0_0_, ai1_.KFOUT as KFOUT0_0_, ai1_.NIDTWS010 as NIDTWS10_0_0_, ai1_.NIDTWS020 as NIDTWS11_0_0_, ai1_.TJOB as TJOB0_0_, ai1_.DBGEBRUIKT as DBGEBRUIKT0_0_, ai1_.IPLAATS as IPLAATS0_0_, ai1_.ISTUK as ISTUK0_0_, ai1_.TJOBMAX as TJOBMAX0_0_, ai1_.OMETHODE as OMETHODE0_0_, ai1_.TJOBMIN as TJOBMIN0_0_, ai1_.NIDTWS340 as NIDTWS19_0_0_, ai1_.KPRIO as KPRIO0_0_, ai1_.RPROJECT as RPROJECT0_0_, ai1_.FVERPL as FVERPL0_0_, ai1_.KUITVPL as KUITVPL0_0_, ai1_.NIDTWS210 as NIDTWS24_0_0_, ai1_.FWERKTIJD as FWERKTIJD0_0_, ai1_.ILEV20 as ILEV26_0_0_, ai1_.NIDTWS300 as NIDTWS27_0_0_, ai1_.IWERKPST as IWERKPST0_0_, ai1_.FWERKLYST as FWERKLYST0_0_, mp2_.NIDTWS066 as NIDTWS1_1_, mp2_.ITOEP as ITOEP2_1_, mp2_.OMS50 as OMS3_2_1_, mp2_.RSTATUS as RSTATUS2_1_, atacqitemo6_.nidtws066 as nidtws6_5_, atacqitemo6_.NIDTWS060 as NIDTWS1_5_, atacqitemo6_.NIDTWS060 as NIDTWS1_2_, atacqitemo6_.IBODY as IBODY1_2_, atacqitemo6_.NIDTWS066 as NIDTWS3_1_2_, atacqitemo6_.nidtws040 as nidtws4_1_2_, atacqitemo6_.NIDTWS040 as NIDTWS5_1_2_, atacqitemo6_.nidtws066 as nidtws6_1_2_, atacqitemo6_.ICHASSIS as ICHASSIS1_2_, atacqitemo6_.IFYON as IFYON1_2_, atacqitemo6_.DBMELD as DBMELD1_2_, atacqitemo6_.IPERSMEL as IPERSMEL1_2_, atacqitemo6_.RHFDTYP as RHFDTYP1_2_, atacqitemo6_.IVMIX as IVMIX1_2_, atacqitemo6_.RPRODLYN as RPRODLYN1_2_, atacqitemo6_.NIDTWS040_2 as NIDTWS14_1_2_, atacqitemo6_.DBAFMELD as DBAFMELD1_2_, atacqitemo6_.IPERSAFMELD as IPERSAF16_1_2_, atacqitemo6_.IPLAFMELD as IPLAFMELD1_2_, atacqitemo6_.IWRKPOSTAFM as IWRKPOS18_1_2_, atacqitemo6_.DBDATST as DBDATST1_2_, atacqitemo6_.NIDTWS300 as NIDTWS20_1_2_, atacqitemo6_.TMWRKFT as TMWRKFT1_2_
from [color=red]TWS060 [/color]this_, TWS040 ai1_, TWS066 mp2_, [color=red]TWS060[/color] atacqitemo6_ where this_.nidtws040=ai1_.NIDTWS040 and this_.nidtws066=mp2_.NIDTWS066 and mp2_.NIDTWS066=atacqitemo6_.nidtws066(+) and this_.IBODY=? and ai1_.IATACQ=?
|