title: How to convert this raw query into a Jpa Query?
body: <p>I have the following raw query:</p>
  
  <pre><code>SELECT CD.* FROM COUNTRY_DETAIL CD, COUNTRY CT where CD.COUNTRY_CODE = CT.COUNTRY_CODE AND CD.LANGUAGE_CODE = 'EN' AND CT.CITIZENSHIP_IND='Y' 
  </code></pre>
  
  <p>In my Java project, the entity <code>CountryDetail</code> corresponds to the <code>COUNTRY_DETAIL</code> table and <code>Country</code> entity corresponds to the <code>COUNTRY</code> table. They are as follows:</p>
  
  <p><strong>CountryDetail.java:</strong></p>
  
  <pre><code>@Entity
  public class CountryDetail implements Serializable
  {
      private static final long serialVersionUID = 1L;
  
      @Id
      @GeneratedValue(strategy = GenerationType.AUTO)
      @Column(name = "COUNTRY_DETAIL_ID")
      private long id; /* id public getter and setter exist */
  
      @Column(nullable = false, length = 2)
      private String languageCode; /* languageCode public getter and setter exist */
  
      @ManyToOne
      @JoinColumn(name = "COUNTRY_CODE", referencedColumnName = "COUNTRY_CODE")
      private Country country; /* country public getter and setter exist */
  
  
      public String getCountryCode(){
          return this.iCountry.getCountryCode();
      }
  
  }
  </code></pre>
  
  <p><strong>Country.Java:</strong></p>
  
  <pre><code>@Entity
  public class Country implements Serializable
  {
      private static final long serialVersionUID = 1L;
  
      @Id
      @GeneratedValue(strategy = GenerationType.AUTO)
      @Column(name = "COUNTRY_ID")
      private long id; /* id public getter and setter exist */
  
      @Column(name="COUNTRY_CODE", nullable = false, unique = true, length = 2)
      private String countryCode; /* countryCode public getter and setter exist */
  
      @Column(nullable = false, length = 60)
      private String officialName; /* officialName public getter and setter exist */
  
      @Column(nullable = true, length = 1)
      private String citizenshipInd; /* citizenshipInd public getter and setter exist */
  
  
      @OneToMany(mappedBy="country")
      private List&lt;CountryDetail&gt; countryDetails = new ArrayList&lt;CountryDetail&gt;();
      public List&lt;CountryDetail&gt; getCountryDetails() {
          return countryDetails;
      }
  
  }
  </code></pre>
  
  <p>How can I translate that raw query into a Jpa Query? My attempt is as follows (the inputs have been parameterized instead of being hardcoded like in the raw query):</p>
  
  <p><code>@Query("SELECT distinct cd FROM CountryDetail cd, Country ct WHERE UPPER(cd.languageCode) = TRIM(UPPER(:langcode)) and UPPER(cd.country.citizenshipInd) = TRIM(UPPER(:citizenshipindicator))")
  </code></p>
  
  <p>And this gives me the expected results (a list of <code>CountryDetail</code>), however that was only after I added the <code>distinct</code> without which the return contained the expected result duplicated several times. Is it possible to get the results without the distinct query or is it unavoidable because of the way JPA works in dealing with interdependent entities?</p>
  
  <p>Thank you.</p>