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?
Consider:
SELECT ct.plate_number AS "Госномер грузовика"
, cf.name AS "Поле погрузки"
, ms.shift_id AS "ID смены"
, ms.trip_id AS "ID рейса"
, * -- typically unnecessary & inefficient to include all columns in the result!
FROM core.monitoring_stages ms
LEFT JOIN org.catalog_truck ct ON ct.catalog_truck_id = ms.vehicle_id -- LEFT JOIN = safe way
LEFT JOIN org.catalog_field cf ON cf.catalog_field_id = ms.field_id
WHERE ms.gen_id = (
SELECT max(gen_id)
FROM core.monitoring_tripmachine_generation
WHERE ready -- boolean can be used directly
AND timerange_from >= '2024-09-23 0:0 +03' -- the day 2024-09-23 filtered properly
AND timerange_to < '2024-09-24 0:0 +03'
)
ORDER BY ms.dtstart;
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;