I want to join columns from several tables. For one table join works fine
select org.catalog_truck.plate_number as "Госномер грузовика",
shift_id as "ID смены",
trip_id as "ID рейса",
*
from core.monitoring_stages
join org.catalog_truck
on vehicle_id = org.catalog_truck.catalog_truck_id
where gen_id = (SELECT max(gen_id)
FROM core.monitoring_tripmachine_generation
WHERE ready=true and timerange_from > '2024-09-23 00:00:00.000 +0300' and timerange_to < '2024-09-23 23:59:59.000 +0300')
ORDER by dtstart
But for two joins (and two selects in the first two lines I have an error)
select org.catalog_truck.plate_number as "Госномер грузовика",
select org.catalog_field.name as "Поле погрузки", <--- select for the second join (error in this line)
shift_id as "ID смены",
trip_id as "ID рейса",
*
from core.monitoring_stages
join org.catalog_truck
on vehicle_id = org.catalog_truck.catalog_truck_id
where gen_id = (SELECT max(gen_id)
from core.monitoring_stages <--- second join
join org.catalog_field cf
on field_id = org.catalog_field.name
FROM core.monitoring_tripmachine_generation
WHERE ready=true and timerange_from > '2024-09-23 00:00:00.000 +0300' and timerange_to < '2024-09-23 23:59:59.000 +0300')
ORDER by dtstart
Error text:
SQL Error [42601]: ERROR: syntax error at or near "select" Position: 66
Error position: line: 2 pos: 65
How to apply two selects for two joins?
You do not need to use multiple selects, once you've join two (or more) tables, you can select anything from the resulting table, like you already did with the monitoring_stages (if I didn't misunderstand your table schemas) :
select
org.catalog_truck.plate_number as "Госномер грузовика",
org.catalog_field.name as "Поле погрузки", -- Second join column
shift_id as "ID смены",
trip_id as "ID рейса",
*
from
core.monitoring_stages
join
org.catalog_truck
on vehicle_id = org.catalog_truck.catalog_truck_id
join
org.catalog_field -- Second table join
on field_id = org.catalog_field.catalog_field_id
where
gen_id = (SELECT max(gen_id)
FROM core.monitoring_tripmachine_generation
WHERE ready = true
and timerange_from > '2024-09-23 00:00:00.000 +0300'
and timerange_to < '2024-09-23 23:59:59.000 +0300')
ORDER by dtstart;