CODEWITHSUNDEEP
sqlpostgresql
user1285928
user1285928

Reputation: 1476

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

Upvotes: 0

Views: 58

Answers (1)

Gab
Gab

Reputation: 3520

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;

Upvotes: 1

Related Questions