How to write jpql query for subquery with multiple conditions and other relationships
<p>I want to fire following query through JPQL.I am new to JPA and learning JPQL but stuck at this query.</p>

<pre><code>SELECT * FROM users
WHERE userid NOT IN (SELECT userid FROM applicants)
AND gender = 'MALE' AND (firstname like '%R%' OR lastname like '%S%');
</code></pre>

<p>Here gender and name parameters are passed dynamically but here i kept it static.</p>

<p>My Classes Snippets are:
This is my user class<br>
<strong>User Class:</strong></p>

<pre><code>@Table(name = "users")
public class User{

@Id
@GeneratedValue
private long userId;

@Column(unique = true, nullable = false)
private String email;

private String firstName;

private String middleName;

private String lastName;

private String gender;

private Timestamp appliedOn;
}
</code></pre>

<p>Applicant class has OneToOne relationship  with User class and join column is userid.</p>

<p><strong>Applicant Class:</strong></p>

<pre><code>@Entity
@Table(name = "applicants")
public class Applicant{

@Id
private long applicantId;

@OneToOne
@JoinColumn(name = "userId")
private User user;

@ManyToOne
@JoinColumn(name = "subCourseId")
private SubCourse course;

@ManyToOne
@JoinColumn(name = "collegeId")
private College college;
}
</code></pre>