CODEWITHSUNDEEP
sqldatabasespring-bootspring-data-jpahql
munir mounir
munir mounir

Reputation: 309

How to get Distinct values with joined table?

I'm going to filter by input value, but when I do this query i get some duplicate values!

    @Query("SELECT  DISTINCT inter FROM Intermediary inter WHERE inter.country=?1 and inter.isGood<>?2 " +
            "and inter.company.name like %?3% " +
            "or inter.company.identity like %?3% " +
            "or inter.company.client like %?3% " +
            "order by inter.createdAt DESC")
    Page<Intermediary> findAllByCompanyAndSearchValue(Country country, Integer isGood, String searchValue, Pageable pageable);

// Intermediary Entity 
{
   // ... Other attributes
  @ManyToOne(optional = false)
    private Company company;
}

// comapny Entity
{
    @OneToMany(mappedBy = "company")
    @JsonIgnore
    private List<Intermediary> intermediaries;

}

Upvotes: 0

Views: 40

Answers (1)

goodbyeyellowbrickroad
goodbyeyellowbrickroad

Reputation: 26

Try adding parentheses around the "or" conditions to avoid returning duplicates resulting from rows that meet any of the possible "and/or" combinations:

"or (inter.company.identity like %?3% " +
"or inter.company.client like %?3% )" +

Upvotes: 1

Related Questions