sqlpostgresqlinner-join

How to apply several selects for several joins?


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?


Solution

  • 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;