Translate SQL query to JPQL

Following query:

SELECT kinder.kindID, kinder.kindName, kinder.kindVorname 
FROM kinder
INNER JOIN (SELECT kindName FROM kinder
    GROUP BY kindN开发者_Python百科ame HAVING count(kindID) > 1) dup ON kinder.kindName = dup.kindName
ORDER BY kindName

finds and displays duplicate candidates from my MySQL DB.

Now i need help translating it to JPQL, no sucess so far.

---

I've just been experimenting, came up with the same solution as JB. This runs in OpenJPA on WebSphere.

    SELECT k.id, k.name, k.vorname from Kinder k where 
        k.name IN (
        SELECT dup.name FROM Kinder dup 
             GROUP BY dup.name  HAVING count(dup.id) > 1)   

---

I'm not sure where subqueries are allowed in JPQL but your query:

SELECT kinder.kindID, kinder.kindName, kinder.kindVorname 
FROM kinder
  INNER JOIN
    ( SELECT kindName
      FROM kinder
      GROUP BY kindName
      HAVING count(kindID) > 1
    ) dup
    ON kinder.kindName = dup.kindName
ORDER BY kinder.kindName

can also be written (besides the IN way) using EXISTS :

SELECT k.kindID, k.kindName, k.kindVorname 
FROM kinder k
WHERE EXISTS
      ( SELECT *
        FROM kinder k2
        WHERE k2.kindName = k.kindName
          AND k2.kindID <> k.kindID
      ) 
ORDER BY k.kindName

or using ANY :

SELECT k.kindID, k.kindName, k.kindVorname 
FROM kinder k
WHERE k.kindID <> ANY
      ( SELECT k2.kindID
        FROM kinder k2
        WHERE k2.kindName = k.kindName
      ) 
ORDER BY k.kindName