Hi,

My system is using mysql database. I am trying to write a left outer join SQL in HQL. I had tried to write it in HQL, but the count is not same as the one executed thru MySQL query browser. My sql query is as follows:

SELECT c.company_name, d.department_name, u.unit_name, count(s.staffno)
FROM staff s
LEFT OUTER JOIN company c on c.id = s.company_id
LEFT OUTER JOIN department d on d.id = s.department_id
AND d.company_id = c.id
LEFT OUTER JOIN unit u on u.id = s.unit_id
AND u.company_id = c.id
AND u.department_id =d.id
GROUP BY c.company_name, d.department_name, u.unit_name

Can anyone help me out? Thanks in advance.

Can you post the HQL?
We are trying something else too.

The SQL in HQL would be:

SELECT company.companyName, department.departmentName, unit.unitName, count(staff.staffNo)
FROM staff in Staff,
company in Company,
department in Department,
unit in Unit
WHERE company.Id = staff.companyId
AND department.Id = staff.departmentId
AND department.companyId = company.Id
AND unit.Id = staff.unitId
AND unit.companyId = company.Id
AND unit.departmentId = department.Id
GROUP BY company.companyName, department.departmentName, unit.unitName