I have a case in which I have to join 3 tables. The situation is like as below:
Now,
a) Asset can have multiple Transactions there is a relationship of OneToMany
.
b) One Transaction can belong to only one Employee So, one to one relation .
--> I have to get List of Assets for one Employee.
My schema is:
a) AssetTbl:
@Id
@ColumnName("ASSETID")
int assetId;
@OneToMany()
@JoinColumn(name="ASSET_ID",referncedColumnName="AssetId")
List<TransactionTbl> trans;
b) TransactionTbl:
@Id
@ColumnName("TRANS_ID")
int transId;
@ColumnName("ASSET_ID")
int assetId;
@OneToOne()
@JoinColumn(name="Emp_ID",referncedColumnName="Emp_Id")
EmployeeTbl emp;
c) EmployeeTbl:
@Id
@ColumnName("Emp_ID")
int empId;
When I join and run Query it gives Exception employeeTbl does not exist in AssetTbl. Is there any problem in my schema?
I believe AssetTbl has transactionId. Transaction table has employeeId as reference.
Please find the below queries to get the assets for an emp.
@Query("select assetTbl from AssetTbl as assetTbl
inner join assetTbl.trans as trans
inner join trans.emp as emp
where emp.empId = :empId)
or
<query name="findAssetByEmployee">
<query-param name="empId" type="long"/>
select assetTbl from AssetTbl as assetTbl
inner join assetTbl.trans as trans
inner join trans.emp as emp
where emp.empId = :empId
</query>