I am trying to convert this native sql into an HQL query. Basically it returns all the contacts that are duplicates (by company, firstname, and lastname). The query below works great, but performing a joined subselect seems impossible in HQL is it not?!
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 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