I have 3 tables (postgreSQL):
Application - id; - describing; ...... - customer_id (many to one).
Also I have the abstract class User with two children classes Customer and Executor, which have different tables in DB.
Customer - id; - name; ...... - locality_id (many to one)
Both Customer and Executor tables have many to one related table Locality, connected via id.
Locality - id; - region.
I try to realize seaching via Application, using (String) keyWord and id (long) region.
SELECT FROM Application a WHERE a.describing LIKE %:keyWord% AND a.locality_id IN (SELECT FROM User u WHERE u.locality_id IN (SELECT FROM Locality L WHERE L.locality_id =: region))
SELECT u FROM Application AS u LEFT JOIN Customer AS c ON c.userid = u.customerid LEFT JOIN Localities AS L ON L.id = c.localitiesid WHERE L.id = :region AND u.applicationshortname LIKE %:keyWord%
As a result I need List /Application/, which have "keyWord" in describing field and region in Application.Customer.locality. Please help
Is this what you're looking for?
SELECT a.* FROM application a JOIN customer c on a.customer_id=c.id JOIN locality l on c.locality_id=l.id WHERE a.description like '%keyWord%';