I am trying to convert the following SQL query to an HQL query, but cannot figure this out. This query bascially finds all the duplicate rows in a Contacts table according to company, firstname, and lastname. The simple "having" and "groupBy" query that I've seen on the web won't work since I need every row that is a duplicate. My sql is below. Is this possible in HQL?
For this HQL there are only two classes that apply: Contact and Team. (Team -> Contact : One -> Many)
Code:
Select c.* from contact c
join
(
Select c2.* from contact c2
group by c2.company, c2.firstname, c2.lastname, c2.teamId
having count(c2.contactId)>1 and c2.teamId=1
) dupes
on c.company=dupes.company
and c.teamId=1
and c.firstname=dupes.firstname
and c.lastname=dupes.lastname
order by c.company, c.firstname, c.lastname
Login
FAQ
Search