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
精彩评论