title: Query in SQL or JPQL to fetch unidirectional relationship One-To-Many
body: <p>I'm building an API for the company where I work and it is my first time using Spring Boot, so I'm having some doubts. So I have a One-To-Many unidirectional relationship, from my parent class (Compania) to my child class (Office). The thing is that I don't know how to query for the Office objects that are part of the Compania object. I think it should be a straightforward solution, but I can't manage to find it.</p>
  <p>Hibernate creates for me an extra table that is called COMPANIA_OFFICE and I don't know how to use it with a SQL or JPQL query. So right now in my database, I have a table called COMPANIA, another one called OFFICE, and the last one called COMPANIA_OFFICE (this one contains COMPANIA_ID and OFFICE_ID, as I don't have the FK in either of the 2 classes it uses an auxiliary table to join them, I suppose).</p>
  <p>What I want to achieve is in a GET request like this one:</p>
  <p><strong>GET /companias/{companiaId}/office/{officeName}</strong> --&gt; Return a <strong>list with the offices</strong> that have that name from a <strong>specified company by id</strong>.</p>
  <p>The classes that I have now are these:</p>
  <p><strong>Compania</strong></p>
  <pre><code>@Entity(name = &quot;Compania&quot;)
      @Table(
          name = &quot;compania&quot;
      )
      public class Compania {
          @Id
          @SequenceGenerator(
              name = &quot;compania_sequence&quot;,
              sequenceName = &quot;compania_sequence&quot;,
              allocationSize = 1
          )
          @GeneratedValue(
              strategy = GenerationType.SEQUENCE,
              generator = &quot;compania_sequence&quot;
          )
          @Column(
              nullable = false
          )
          private Long id;
      
          @Column(
              name = &quot;name&quot;,
              nullable = false,
              unique = true
          )
          private String name;
      
          @OneToMany(
              mappedBy = &quot;compania&quot;,
              cascade = CascadeType.ALL,
              fetch = FetchType.LAZY,
              orphanRemoval = true
          )
          private List&lt;Office&gt; office;
  
      ...Constructors...
      ... Getters and Setters...
  </code></pre>
  <p><strong>Office (Child)</strong></p>
  <pre><code>@Entity()
  @Table()
  public class Office {
      @Id
      @SequenceGenerator(
          name = &quot;office_sequence&quot;,
          sequenceName = &quot;office_sequence&quot;,
          allocationSize = 1
      )
      @GeneratedValue(
          strategy = GenerationType.SEQUENCE,
          generator = &quot;office_sequence&quot;
      )
      @Column(
          nullable = false
      )
      @JsonIgnore
      private Long id;
  
      @Column(
          name = &quot;idRef&quot;,
          nullable = false
      )
      private int idRef;
  
      @Column(
          name = &quot;title&quot;,
          nullable = false
      )
      private String title;
  
      @Column(
          name = &quot;name&quot;
      )
      private String name;
  
      ...Constructors...
      ... Getters and Setters...
  </code></pre>
  <p>So my problem is that I don't know how to make that query in spring Boot. I don't if using straight SQL or JPQL.</p>
  <p>Thanks in advance!</p>
  <p>Edit. I've tried using a native Query like this:</p>
  <pre><code>@Query(
          value = &quot;select o.* from office o &quot; +
              &quot;inner join compania_office co on o.id = co.office_id &quot; +
              &quot;inner join compania c on c.id = co.compania_id &quot; +
              &quot;where c.id = :companiaId and lower(o.title) like lower(:officeTitle) &quot; +
              &quot;order by o.id_ref asc;&quot;,
          nativeQuery = true
      )
      List&lt;Office&gt; findOfficesByTitleLike(@Param(&quot;companiaId&quot;)Long companiaId,@Param(&quot;officeTitle&quot;) String officeTitle);
  </code></pre>
  <p>It works when I'm using a Database manager like Navicat or the one embedded in IntelliJ doing a common query. But when I try to use it with my API it gives me this error:</p>
  <blockquote>
  <p>org.springframework.dao.InvalidDataAccessResourceUsageException: could
  not extract ResultSet; SQL [n/a]; nested exception is
  org.hibernate.exception.SQLGrammarException: could not extract
  ResultSet</p>
  </blockquote>
  <p>Do you know what could be happening? Thanks!</p>