duplicate [ID] alias error for sql statement with join-CodePudding

I created a search button with a SQL query which is including JOIN 2 times, but its throwing error like:

org.hibernate.loader.NonUniqueDiscoveredSqlAliasException: Encountered a duplicated sql alias ID during auto-discovery of a native-sql query

This is the Repository method:

@Query(value = "select * from reports r join patients p on r.patient_id = p.id join lab_technicians lt on r.lab_technician_id = lt.id where p.name like '%:patientName%' and lt.name like '%:labTechnicianName%' and p.identity_no like '%:patientIdentityNo%'", nativeQuery = true)     
List<Report> findBySearch(@Param("patientName") String patientName, @Param("labTechnicianName") String labTechnicianName, @Param("patientIdentityNo") String patientIdentityNo);

To make you understand the project templates, these are the entities classes:

Person class:
  @MappedSuperclass     
   public abstract class Person {     
   @Id     
   @GeneratedValue(strategy = GenerationType.IDENTITY)     
   private Long id;     
   @Column(name = "name")     
   private String name;



Patient class:

    @Entity     
    @Table(name = "patients")     
    public class Patient extends Person{     
    @Column(name = "identity_no")     private String identityNo;     
    @OneToMany(mappedBy = "patient")     private List<Report> reports;



LabTechnician class:
   

    @Entity     
    @Table(name = "lab_technicians")     
    public class LabTechnician extends Person{     
    @Column(name = "hospital_id")     
    private String hospitalId;     
    @OneToMany(mappedBy = "labTechnician")     
    private List<Report> reports;




and lastly Report class:
   

    @Entity

    @Table(name = "reports")
    public class Report {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne
    @JoinColumn(name = "lab_technician_id")
    private LabTechnician labTechnician;
    
    @ManyToOne
    @JoinColumn(name = "patient_id")
    private Patient patient;
    
    @Column(name = "file_number")
    private String fileNumber;
    
    @Column(name = "diagnostic")
    private String diagnostic;
    
    @Column(name = "detail_of_diagnostic")
    private String detailOfDiagnostic;
    
    @Column(name = "date_of_report")
    private Date dateOfReport;

I changed List in @OneToMany relationships to Set but its not changed anything

CodePudding user response：

 @Query(value = "select * from reports r join patients p on r.patient_id = 
 p.identity_no join lab_technicians lt on r.lab_technician_id = 
 lt.hospital_id where p.name like '%:patientName%' and lt.name like 
'%:labTechnicianName%' and p.identity_no like '%:patientIdentityNo%'", 
 nativeQuery = true)     
 List<Report> findBySearch(@Param("patientName") String patientName, 
 @Param("labTechnicianName") String labTechnicianName, 
 @Param("patientIdentityNo") String patientIdentityNo);

Use this query it will work fine