0 Replies Latest reply on Dec 12, 2006 7:21 AM by Ahmad Atwi

    Inner Join

    Ahmad Atwi Newbie

      Dear all:
      I have 3 tables in the database (Companies, Departments and Extensions)
      There is a One-Many relation between Companies and Extensions
      a One-Many relation between Companies and Departments
      and a One-Many relation between Departments and Extensions

      I am trying to implement a seach function. this function will look for requested input in all the fields of the 3 tables and return the result as list. (ie. return a list of extensions and in this list a link to its departments and Companies).

      i am trying 2 create an inner join between the extensions table and the other 2 tables, but that is not working it is returning an empty list.
      when i create an inner join between the extensions table and one of the other 2 tables then a the query is returning the correct result
      here is part of my code

      queryString.append (" select extension from Extensions extension");
      queryString.append (" inner join extension.companies as company");
      queryString.append (" inner join extension.departments as department");
      queryString.append (" where extension.fullname like :searchQuery");
      queryString.append (" or extension.extension like :searchQuery");
      parameters.put ("searchQuery", '%' + example.getFullname() + '%');
      

      any idea how to solve that

      Best Regards
      ama55