2 Replies Latest reply on Jun 1, 2004 2:37 PM by aloubyansky

    Missing join in SQL query with EJB-QL ORDER BY clause (jBoss


      The following jBoss-QL query (jBoss 3.2.2):
      SELECT OBJECT(fr) FROM FilmRental fr, Principal p WHERE p.id = ?1 AND fr.location MEMBER OF p.locations AND fr.year = ?2 AND fr.week = ?3 ORDER BY fr.film.name ASC

      results in the following SQL query:
      SELECT t0_fr.film_num, t0_fr.location_num, t0_fr.weeknum, t0_fr.year, t8_fr_film.film_name FROM filmrental t0_fr, principals t1_p, film t8_fr_film, location t4_fr_location WHERE (t1_p.principalid = ? AND EXISTS (SELECT t3_p_locations_RELATION_TABLE.location_num FROM principal_location t3_p_locations_RELATION_TABLE WHERE t1_p.principalid=t3_p_locations_RELATION_TABLE.principalid AND t4_fr_location.location_num=t3_p_locations_RELATION_TABLE.location_num) AND t0_fr.year = ? AND t0_fr.weeknum = ? AND t0_fr.location_num=t4_fr_location.location_num) ORDER BY t8_fr_film.film_name ASC

      For some reason table filmrental t0_fr is not joined with film t8_fr_film.
      Can someone shed some light on this?