I have these Postgres tables:
create table deals_new ( id bigserial primary key, slip_id text, deal_type integer, timestamp timestamp, employee_id bigint constraint employee_id_fk references common.employees ); create table twap ( id bigserial primary key, deal_id varchar not null, employee_id bigint constraint fk_twap__employee_id references common.employees, status integer ); create table common.employees ( id bigint primary key, first_name varchar(150), last_name varchar(150) );
I use this SQL query:
select d.*, t.id as twap_id from common.deals_new d left outer join common.twap t on t.deal_id = d.slip_id and d.timestamp between '11-11-2021' AND '11-11-2021' and d.deal_type in (1, 2) and d.quote_id is null where d.employee_id is not null order by d.timestamp desc, d.id offset 10 limit 10;
How I extend this SQL query to search also in table employees
by employee id and filter the result by any character found in table columns first_name
and last_name
?
Advertisement
Answer
You can join the new table using employee_id
:
SELECT d.*, t.id as twap_id, e.first_name, e.last_name FROM common.deals_new d JOIN common.employees e ON e.id = d.employee_id -- Joining employees table LEFT OUTER JOIN common.twap t on t.deal_id = d.slip_id AND d.timestamp between '11-11-2021' AND '11-11-2021' AND d.deal_type in (1, 2) AND d.quote_id is null WHERE ( -- Filtering based on first_name or last_name e.first_name LIKE '%John%' OR e.last_name LIKE '%Doe%' ) ORDER BY d.timestamp desc, d.id OFFSET 10 LIMIT 10;