Select from table using parameters from related tables

by Никита   Last Updated October 16, 2019 20:26 PM - source

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.

First approach:

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))

Second approach:

    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



Answers 1


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%';
richyen
richyen
October 16, 2019 20:25 PM

Related Questions




Accessing PostgreSQL 9.6 from Spring Boot in CircleCi

Updated September 17, 2017 19:26 PM

Heroku don't create table on postgresql

Updated June 04, 2017 21:26 PM