Fetching data from 2 tables based on the id criteria (primary id of one table is saved in other tables column)
Asked on December 07, 2017
I want to fetch employee details from employee table and employee type name from employee_type table in one go (fetch in one api call),
Replied on December 07, 2017
What is table structure and what HQL have you tried yet?
Replied on December 07, 2017
I want the hql query for my following sql query
select * from core_employee emp, employee_type empt where emp.deleted=0 and emp.emp_type_id=empt.pk_empt_id;
Replied on December 08, 2017
Suppose entity for core_employee is CoreEmployee and entity for employee_type is EmployeeType, your HQL could be as following
FROM CoreEmployee as emp, EmployeeType as empt WHERE where emp.deleted=0 and emp.empTypeId=empt.EmptId;
Replied on December 10, 2017
@Kamal thank you it got worked.
Replied on December 10, 2017
I am getting json response in array of array format how do i get in single array.
My json responce is as follows,
[[{"pk_emp_id":5,"tenant_id":"Zone1","location_id":1,"emp_number":"sk44","prefix":"","first_name":"qqqqq","middle_name":"www","last_name":"eeee","display_name":"qqqq","full_name":"qqq qqqq","email":"qqqq@gmail.com","gender":"Female","emp_type_id":2,"date_of_hire":191000,"date_of_birth":null,"manager_id":7,"phone_number":"9877654","position":"SE","responsibility":"","notes":"","contracted":"0","street":"vidyanagar","state":"Karnataka","city":"hubli","zip_code":"9898","dob":-19800000,"ssn":"","deleted":0},{"pk_empt_id":2,"empt_tenant_id":"2","empt_name":"temporary","deleted":0}]]
and service implantation is as follows,
@SuppressWarnings("unchecked")
@Override
public List<Employee> getAllEmployees() {
//return employeeRepository.findAll();
Query query = em.createQuery("FROM Employee as emp, EmployeeType as empt WHERE emp.deleted=0 and emp.emp_type_id=empt.pk_empt_id");
return (List<Employee>) query.getResultList();
}
Replied on December 11, 2017
Try selecting only employee.
SELECT emp FROM Employee as emp, EmployeeType as empt WHERE emp.deleted=0 and emp.emp_type_id=empt.pk_empt_id
Replied on December 11, 2017
If i select only emp from employee.. am getting data from only employee table and not from both employee and employee_type tables.
