[FIXED] How do I do a JPQL SubQuery?

Issue

It is possible to do the equivalent of this sql query in JPQL?

SELECT * 
 FROM COUNTRIES c WHERE COUNTRY_ID IN (
  SELECT DISTINCT COUNTRY_ID 
   FROM PORTS p 
   WHERE p.COUNTRY_ID = c.COUNTRY_ID AND STATE = 'A'
) 

Solution

You need to test it with IN and subquery since both do work in JPQL (according to syntax reference they do work together). You may also look at MEMBER OF expressions.

But there is a better approach in my opinion. Such queries are called correlated sub-queries and one can always re-write them using EXISTS:

SELECT * FROM COUNTRIES c WHERE 
EXISTS (
        SELECT 'found' FROM PORTS p 
        WHERE p.COUNTRY_ID = c.COUNTRY_ID AND STATE = 'A'
) 

JPQL supports EXISTS with subqueries.

Answered By - topchef
Answer Checked By - Senaida (JavaFixing Volunteer)