Skip to content

Add join and search in columns for any string

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.*, 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,
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?



You can join the new table using employee_id:

SELECT d.*, as twap_id, e.first_name, e.last_name
FROM common.deals_new d 
JOIN common.employees e ON = 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,
8 People found this is helpful