JPA JPQL nested select statement

I have a very simple join table relating two tables, to simplify the problem we can say table1 is A and table 2 is B, with a join table AB.

A	AB	B
A_id	AB_id	B_id
A_details	A_id_fk	B_details
	B_id_fk

where A_id_fk and B_id_fk are foreign keys respectively. Im trying to create a query to retrieve all rows of A that have a relation to B. So my function receives B_id as an argument, and I want to search AB to get all rows where B_id_fk == b_id, and then use search A for all rows where A_id == the A_id_fk returned from previous search.

I tested and it can be done in plain SQL using nested select like this:

SELECT * 
FROM A 
WHERE A_ID = 
   (SELECT A_id_fk 
   from AB 
   where B_id_fk = B_id);

So i read the documentation for JPQL: http://openjpa.apache.org/builds/1.2.0/apache-openjpa-1.2.0/docs/manual/jpa_langref.html#jpa_langref_exists and tried this

@Transactional
    public List<A> getA(int B_id){
        TypedQuery<A> query = em.createQuery("select i from A i where i.A_id = 
            (select z.A_id_fk from AB z where z.B_id_fk = :B_ID)", A.class);
        return query.setParameter("B_ID",B_id).getResultList();
    }

Im really lost right now because i followed the given example in the documentation, But its giving me unable to resolve z.A_id.fk errors, is there maybe a way to do B.class nested within A.class? Im not sure what im supposed to do here.

from Recent Questions - Stack Overflow https://ift.tt/3n83yyY
https://ift.tt/eA8V8J