title: How to implement JPQL query?
body: <p>I have working query in PostgreSQL:</p>
  
  <pre><code>select s.id, s.seat_number as available_seat, s.row_number as available_row, rm.room_name as screening_room
  from seats s
           join rooms rm on rm.id=s.room_id
           left join (
      select r.seat_id from reserved_seats r
                                join reservations  res on res.id=r.reservation_id AND res.screening_id = 3 ) res on res.seat_id=s.id
  where res.seat_id is null AND s.room_id=3
  ORDER BY s.id;
  </code></pre>
  
  <p>But I make mistakes translating it into the JPA query language.</p>
  
  <p><a href="https://i.stack.imgur.com/ZRDCz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ZRDCz.png" alt="enter image description here"></a></p>
  
  <p>Can I use nested SELECTs in JPQL? </p>
  
  <p><strong>The answer is to use native query</strong>:</p>
  
  <pre><code>@Query(value =
              "SELECT s.id seatId, s.seat_number availableSeat, " +
                      "s.row_number availableRow, rm.name screeningRoom \n" +
                      "FROM seats s\n" +
                      "JOIN rooms rm on rm.id=s.room_id\n" +
                      "   LEFT JOIN (\n" +
                      "       SELECT r.seat_id FROM reserved_seats r\n" +
                      "       JOIN reservations res ON res.id=r.reservation_id " +
                      "       AND res.screening_id = :screeningId) res ON res.seat_id=s.id\n" +
                      "WHERE res.seat_id IS NULL AND s.room_id=:roomId AND s.row_number=:rowNumber\n" +
                      "ORDER BY s.id;", nativeQuery = true)
  </code></pre>