How to get Distinct values with joined table?

Question

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;

}

Answer 1

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% )" +