[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - Inner Join

[ama55]

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

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3993050#3993050

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=3993050