JPA Criteria Builder query with inner SELECT statement

I need to create the below SQL join condition using JPA criteria builder,

SELECT * FROM student s1
INNER JOIN
(SELECT subject,teacher,MIN(marks) AS marks FROM student GROUP BY subject, teacher) s2
ON s1.subject = s2.subject
AND s1.teacher = s2.teacher
AND s1.marks = s2.marks

Below is the entity class and JPA query builder.

@Entity
@JsonIgnoreProperties(ignoreUnknown = true)
public class Student implements Serializable {
    
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name="id")
    Long id;
    
    @Column(name="name")
    String name;
    
    @Column(name="subject")
    public
    String subject;
    
    @Column(name="teacher")
    String teacher;
    
    @Column(name="marks")
    String marks;
    
    @JsonIgnore
    @ManyToOne
    @JoinColumns({
    @JoinColumn(insertable=false, updatable=false, name="subject",referencedColumnName="subject"),
    @JoinColumn(insertable=false, updatable=false, name="teacher",referencedColumnName="teacher"),
    @JoinColumn(insertable=false, updatable=false, name="marks",referencedColumnName="marks")
    })
    Student studentSelf;
    
    @JsonIgnore
    @OneToMany(cascade = CascadeType.ALL, mappedBy="studentSelf")
    Set<Student> studentref;

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Student> query = cb.createQuery(Student.class);
Root<Student> mainStudent = query.from(Student.class);
List<Predicate> predicates = new ArrayList<>();

Join<Student, Student> studentJoin = mainStudent.join("studentSelf", JoinType.INNER);
        
List<Student> list = entityManager.createQuery(query).getResultList();

I am able to build the query with join condition but cannot create the inner SELECT query. How can i create a inner select query for join clause?

Requirement description: The below is the input table and output required.

Answer

I have not tried this and it cloud fail miserably with an exception. But Can you give a try?

• Remove the studentSelf and studentRef if you added them just for this query. @ManyToOneonstudentSelf` is not either as it will point to many records

• I think the following query is equivalent to what you are trying to achieve.

    SELECT * 
    FROM student s1
    WHERE s1.marks 
    IN
    (
      SELECT MIN(s2.marks) 
      FROM student s2
      where s1.subject = s2.subject
      AND  s1.teacher = s2.teacher
    )

• Then I am trying to translate it to CriteriaQuery

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Student> query = cb.createQuery(Student.class);
    Root<Student> mainStudent = query.from(Student.class);

    Subquery<Student> subQuery = query.subquery(Student.class);
    Root<Student> subQueryRoot = subQuery.from(Student.class);

    Predicate predicate1 = cb.equal(mainStudent.get("teacher"), 
                                    subQueryRoot.get("teacher"));
    Predicate predicate2 = cb.equal(mainStudent.get("subject"), 
                                    subQueryRoot.get("subject"));
    Predicate finalPredicate = cb.and(predicate1, predicate2);

    subQuery.select(cb.min(subQueryRoot.get("marks"))).where(finalPredicate);

    query.select(mainStudent).where(mainStudent.get("marks").in(subQuery));
    em.createQuery(issueQuery).getResultList();

Update

Updated subquery.correlate(mainStudent); to subQuery.from(Student.class); based on vinay-s-g comments