Hi,

To generate HQL of following Sql Server 2000

select * from T_CX1_Patient_T patient
inner join T_CX1_PtSite_T site on patient.PatID = site.PatID
inner join T_CX1_Participant_M participant on site.ParticipantId = participant.ParticipantId where Registrytype = 'ACC2.0' and Siteid=1 and (patient.ssn=11 or patient.PatLName='Last')

It requires inner join on three tables .

Please help me out

No way to tell without your mappings

Hbm file is as follows

<class name="com.ge.med.registries.acc_30.hibernate.TCx1ParticipantM" table="T_CX1_Participant_M">
<id name="primary" column="ParticipantID" type="string">
<generator class="assigned"/>
</id>
<property name="participantname" column="ParticipantName" type="string" length="100"/>
<property name="zippassword" column="ZipPassword" type="string" length="50"/>
<property name="registrytype" column="RegistryType" type="string" length="50" not-null="true"/>
<property name="siteId" column="SiteID" type="integer" length="10" not-null="true"/>
<bag name="ptSites" inverse="true" lazy="true" cascade="all" table="T_CX1_PtSite_T">
<key column="ParticipantID"/>
<one-to-many class="com.ge.med.registries.acc_30.hibernate.TCx1PtsiteT"/>
</bag>
</class>

<class name="com.ge.med.registries.acc_30.hibernate.TCx1PatientT" table="T_CX1_Patient_T">
<id name="primary" column="PatID" type="long">
<generator class="native"/>
</id>
<property name="patfname" column="PatFName" type="string" length="50"/>
<property name="patminit" column="PatMInit" type="string" length="1"/>
<property name="patlname" column="PatLName" type="string" length="50"/>
<property name="ssn" column="SSN" type="string" length="50"/>
<property name="gender" column="Gender" type="int" length="3"/>
<property name="dob" column="DOB" type="timestamp" length="23"/>
<property name="medrecnum" column="MedRecNum" type="string" length="50"/>
<property name="phonenumber" column="PhoneNumber" type="string" length="50"/>
<property name="address" column="Address" type="string" length="100"/>
<property name="city" column="City" type="string" length="50"/>
<property name="state" column="State" type="string" length="50"/>
<property name="zip" column="Zip" type="string" length="50"/>
<bag name="site" inverse="true" lazy="true" table="T_CX1_PtSite_T" cascade="all">
<key column="PatID" />
<one-to-many class="com.ge.med.registries.acc_30.hibernate.TCx1PtsiteT"/>
</bag>

</class>

<class name="com.ge.med.registries.acc_30.hibernate.TCx1PtsiteT" table="T_CX1_PtSite_T">
<id name="primary" column="PatSiteID" type="long">
<generator class="native"/>
</id>
<many-to-one name="patId" column="PatID" class="com.ge.med.registries.acc_30.hibernate.TCx1PatientT" not-null="true"/>
<many-to-one name="participantId" column="ParticipantID" class="com.ge.med.registries.acc_30.hibernate.TCx1ParticipantM" not-null="true"/>
<bag name="tcxAdmDisTs" lazy="true" inverse="true" cascade="all" table="T_CX1_AdmDis_T">
<key column="PatSiteID" />
<one-to-many class="com.ge.med.registries.acc_30.hibernate.TCx1AdmdisT" />
</bag>
<one-to-one name="accPatient" class="com.ge.med.registries.acc_30.hibernate.TA3PatientT" constrained="true" outer-join="false" />
</class>

Any other information , be in loop ?