Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Join multiple tables in HQL

Tags:

hibernate

hql

Can anyone help me to convert this query in to HQL

SELECT      
  supplierOrderDetails.productID,
  supplierOrderDetails.orderQty,
  supplierOrderReceiveDetail.qtyArrived
FROM
 supplierOrder
INNER JOIN
  supplierOrderDetails
ON
  (supplierOrderDetails.supplierOrderID = supplierOrder.ID)
INNER JOIN 
    supplierOrderReceive
ON  
 (supplierOrderReceive.supplierOrderID = supplierOrder.ID)
INNER JOIN 
    supplierOrderReceiveDetail
ON  
 (supplierOrderReceiveDetail.supplierOrderReceiveID = supplierOrderReceive.ID)  
   AND 
 (supplierOrderReceiveDetail.ProductID =supplierOrderDetails.ProductID)     
WHERE supplierOrder.ID = 1

Here is the table relationship

enter image description here

like image 482
ZAJ Avatar asked Dec 24 '13 09:12

ZAJ


People also ask

Can we use join in HQL query?

HQL Join : HQL supports inner join, left outer join, right outer join and full join. For example, select e.name, a. city from Employee e INNER JOIN e.

How to join two tables in Hibernate?

Here, we use the @JoinTable annotation to specify the details of the join table (table name and two join columns - using the @JoinColumn annotation); and we set the cascade attribute of the @OneToMany annotation so that Hibernate will update the associated articles when the category is updated.

Can we use group by in HQL?

The HQL Group By clause is used to group the data from the multiple records based on one or more column. It is generally used in conjunction with the aggregate functions (like SUM, COUNT, MIN, MAX and AVG) to perform an aggregation over each group.

What is cross join in HQL?

More than one entity can also appear in HQL which will perform cartesian product that is also known as cross join.


1 Answers

I have added the query for those who want to know how to JOIN multiple tables in HQL

SELECT supplierOrderDetails.productID as product, supplierOrderDetails.orderQty as orderedQty,sum(supplierOrderReceiveDetail.qtyArrived) as qtyArrived 

FROM SupplierOrder as so, SupplierOrderDetails as supplierOrderDetails,  SupplierOrderReceiveDetail as supplierOrderReceiveDetail, SupplierOrderReceive as supplierOrderReceive

INNER JOIN supplierOrderDetails.supplierOrderID

INNER JOIN supplierOrderReceive.supplierOrder

INNER JOIN supplierOrderReceiveDetail.supplierOrderReceive

GROUP BY supplierOrderDetails.productID, supplierOrderDetails.orderQty
like image 114
ZAJ Avatar answered Oct 04 '22 21:10

ZAJ