Search code examples
jpaspring-data-jpadistinctjpql

How to solve duplicated rows in the JPQL query with many-to-one?

My query almost works but it returns duplicate rows, because the team name is duplicated. How can I solve this problem?

I have : "team glass", "team foot", and "team swim". TeamUserCompany has a many-to-one relationship to Team. When I query for team names containing "team" I get duplicated rows because it contains "team glass", "team foot", and "team swim"!

enter image description here

In the front end I need to show companies!

@Query("SELECT distinct teamUserComp FROM TeamUserCompany teamUserComp WHERE teamUserComp.team.name like %?1% ");

Entity

public class TeamUserCompany extends AbstractEntity {
    @ManyToOne(optional = false)
    private Company company;

    @ManyToOne
    private User user;

    @ManyToOne
    private Team team;
}
Solution

  • I used a Group by it solved the problem.

    @Query("SELECT distinct teamUserComp FROM TeamUserCompany teamUserComp WHERE teamUserComp.team.name like %?1% group by (teamUserInter.company)");