Get user from table based on id-CodePudding

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 employees
(
    id              bigint primary key,
    account_id      integer,     
    first_name      varchar(150),
    last_name       varchar(150)
);


New table to query:

create table accounts
(
    id               bigint primary key,
    account_name     varchar(150) not null
);

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 can extend this SQL query to search also in table employees by account_id and map the result in table accounts by id? I would like to print also accounts. account_name based on employees .account_id.

CodePudding user response：

You need two joins to to make this work for you. One join to get to the employee table, and one more join to get to the accounts table.

select d.*, t.id as twap_id, a.account_name
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
    join employees as e on d.employee_id = e.id
    join accounts as a on a.id = e.account_id 
where d.employee_id is not null
order by d.timestamp desc, d.id
offset 10
limit 10;

Note: I did not fiddle this one, so could have a typo, but I think you get the idea here.