CODEWITHSUNDEEP
javajpajpql
Jacob
Jacob

Reputation: 14731

JPQL join condition in linked Entity class

I'm using the following SQL to join employees, user_projects and project_master 

SELECT DISTINCT usr.user_number,
                  emp.emp_name
    FROM employees emp
         LEFT JOIN user_projects usr
            ON (emp.user_number = usr.user_number)
         JOIN project_master mast
            ON (usr.project_id = mast.project_id)
   WHERE     mast.active = 'Y'
         AND emp.user_number = 'SMITH'

In Employee entity, I have the following JPQL defined as namedQuery

@NamedQuery(name = "Employee.findProjects", query = " select DISTINCT u.userNumber,e.empName" +
" from Employee e LEFT JOIN e.userProjectsList u  where e.userNumber='SMITH' ")

Not sure how to link UserProjects and ProjectMaster to create a where condition mast.active = 'Y' in Employee entity's findProjects namedQuery How to join UserProjects and ProjectMaster in Employee Entity class?

Entities

Entity Employee

@Table(name = "EMPLOYEES")
public class Employee implements Serializable {

@Id
@Column(name = "USER_NUMBER", nullable = false)
private String userNumber;

@Column(name = "EMP_NAME")
private String empName;

@OneToMany(mappedBy = "employee")
private List<UserProjects> userProjectsList;

Entity UserProjects

@Table(name = "USER_PROJECTS")
public class UserProjects implements Serializable {

@Id
@Column(name="PROJECT_ID", nullable = false, insertable = false, 
    updatable = false)
private String projectId;

@Id
@Column(name="USER_NUMBER", nullable = false, insertable = false, updatable = false)
private String userNumber;

@ManyToOne
@JoinColumn(name = "PROJECT_ID", referencedColumnName = "PROJECT_ID")
private ProjectMaster projectMaster;

Entity ProjectMaster

@Table(name = "PROJECTMASTER")
public class ProjectMaster implements Serializable {

@Id
@Column(name="PROJECT_ID", nullable = false)
private String projectId;

@Column(name="PROJECT_DESCRIPTION")
private String projectDesc;

@Column(name="ACTIVE")
private String active;

Upvotes: 1

Views: 1436

Answers (1)

Stan
Stan

Reputation: 1430

select DISTINCT u.userNumber,e.empName from Employee e LEFT JOIN  
    e.userProjectsList u  JOIN u.projectMaster pm
    where e.userNumber='SMITH' AND pm.active='Y'

Isn't this work?

Upvotes: 2

Related Questions