CustomerPasswordHistory Table is a table where no unique customerId. customerId is duplicate how to join customer table unique id in java

Question

CustomerPasswordHistory Table

Id	CustomerId	PasswordChangeBy
1	1182	ABC
2	1182	CDE

Customer Table

Id	firstName	userIdentity
1182	XYZ	01231923123
2345	asda	12093109231

Expected Outcome

FirstName	PasswordchangeBy	userIdentity
XYZ	ABC	01231923123
2345	asda	12093109231

How to find expected outcome using java native query

@Query(value = "select c.first_name, cph.password_change_by, c.user_identity  from wallet_db.customer.customer_password_history cph " +
        "inner join wallet_db.customer.customer c on cph.customer_id = c.id ", nativeQuery = true)

if run this query java throw unique value exception

Answer 1

    import lombok.*;

import java.io.Serializable;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class CustomerPasswordHistoryResponse implements Serializable {
    private String firstName;
    private String passwordChangeBy;
    private String userIdentity;
}

@Query("select new com.sdfsdf.customer.domain.response.CustomerPasswordHistoryResponse(c.firstName, cph.passwordChangeBy, c.userIdentity) from CustomerPasswordHistory cph " +
        "inner join Customer c on cph.customerId = c.id ")
List<CustomerPasswordHistoryResponse> getAllHistory();