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() + '%');