Postgresql - How to apply several selects for several joins?

mascai
September 24, 2024
133 views
0 votes
2 Answers

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?

Answers

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;

Consider:

SELECT ct.plate_number AS "Госномер грузовика"
     , cf.name AS "Поле погрузки"
     , ms.shift_id AS "ID смены"
     , ms.trip_id AS "ID рейса"
     , *  --  typically unneseccary & 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;

Please signup or login to give your own answer.

Click here to cancel reply.

Postgresql – How to apply several selects for several joins?

Answers