Hi,

how can i translate the following SQL command to HQL

select * from artikel LEFT OUTER JOIN kundenpreise
ON artikel.id = kundenpreise.artikel_nr
and kundenpreise.kunde_nr='1000'
and artikel.nur_kunde=1
where ( artikel.nur_kunde = 1 AND kundenpreise.artikel_nr <> ''
OR artikel.nur_kunde = 0 )

i tried the following HQL
select artikel from ArtikelDBO artikel
LEFT OUTER JOIN artikel.kdPreise kdp
where ( artikel.nurKunde = 1 AND kdp.artikelNr <> ''
AND kdp.kundeNr ='1000' OR artikel.nurKunde = 0 )

but then i get more than one record for an artikel if there are many records with the same artikelNr and different kundeNr.

i have the following mapping files:

<hibernate-mapping>
<class
name="de.dlsoft.ArtikelDBO"
table="artikel"
dynamic-update="false"
dynamic-insert="false"
>
<id name="id" column="id" type="java.lang.String" >
<generator class="assigned" />
</id>
<property name="beschreibung" type="java.lang.String" />
<property name="gesperrt" type="boolean" />
<property name="kurzname" type="java.lang.String" />
<property name="nurKunde" type="boolean" />
<set name="kdPreise" lazy="true" inverse="false"
cascade="none" >
<key column="artikel_nr" />
<one-to-many
class="de.dlsoft.KundenpreiseDBO"
/>
</set>
</class>

<class
name="de.dlsoft.KundenpreiseDBO"
table="kundenpreise"
dynamic-update="false"
dynamic-insert="false" >
<id name="idObject" type="java.lang.Integer" >
<generator class="native" />
</id>
<property name="artikelNr" type="java.lang.String" />
<property name="kundeNr" type="java.lang.String" />
</class>

</hibernate-mapping>


Thanks you for your help

http://www.hibernate.org/117.html#A11