JPA Query with GROUP BY, HAVING and COUNT
<p>So the query below is probably not the most efficient, buy still, I am wondering why it is returning no result, even though the SQL counterpart does. There is no error, I am just getting no result. Is it maybe not the correct equivalent for the query I wrote in MySQL?</p>

<p>This is the JPA JPQL.</p>

<pre><code>Query query = em.createQuery("SELECT sub FROM Subscription sub WHERE "
                + "sub.isSuspended = 0 AND "
                + "(SELECT i FROM Invoice i WHERE i.dateDue &lt; CURRENT_DATE AND i.datePaid IS NULL "
                + "GROUP BY i HAVING COUNT(i.idInvoice) &gt; 2) MEMBER OF sub.invoices");
</code></pre>

<p>And this is the SQL from MySQL.</p>

<pre><code>SELECT * from subscription 
WHERE subscription.is_suspended = 0 AND id_subscription IN 
    (SELECT id_subscription FROM invoice 
    WHERE date_due &lt; CURDATE() AND date_paid IS NULL
    GROUP BY id_subscription
    HAVING COUNT(*) &gt; 2)
</code></pre>