Postgres Query Assistance Required

Question

I was wondering is there a better way to write this particular query

SELECT flights.id as flightid,
                flights.icao, 
                aircraft.reg_no AS regno,
                starttime,
                endtime,
                originloc.country_code as origincountry,
                originloc.name_en AS originloc,
                destloc.country_code as destcountry,
                destloc.name_en AS destloc,
                (select pings.call from pings where pings.call != 'null' and pings.flightid = flights.id limit 1) as flightcall
                 FROM flights LEFT JOIN localities_sys originloc ON originloc.id = flights.originloc LEFT JOIN localities_sys destloc
                 ON destloc.id = flights.destloc LEFT JOIN aircraft ON aircraft.icao = flights.icao

If I try to add a condition at the end such as

 WHERE flightcall = 'test'

I get an error stating that ERROR: column "flightcall" does not exist

Any help is greatly appreciated

Thanks Damien

Your flightcall subquery is using LIMIT without ORDER BY, which is fairly meaningless. Please explain the logic of this subquery. — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 29, 2020 at 12:19
there are multiple entries in the ping table for each flight. I want to return only 1 of the calls. The calls colum will be the same in each row however there are some nulls in there unfortunately — Damien
– Damien, Commented Mar 29, 2020 at 12:25
What is the logic for which one call gets selected, or does it truly not matter? — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 29, 2020 at 12:35

Ankit Bajpai · Accepted Answer · 2020-03-29 12:31:00Z

1

Alises in where clause are not supported in PostgreSQL. You either have to use the nested query or use below query -

SELECT flights.id as flightid,
       flights.icao, 
       aircraft.reg_no AS regno,
       starttime,
       endtime,
       originloc.country_code as origincountry,
       originloc.name_en AS originloc,
       destloc.country_code as destcountry,
       destloc.name_en AS destloc,
       (select pings.call
        from pings
        where pings.call != 'null'
        and pings.flightid = flights.id
        limit 1) as flightcall
FROM flights
LEFT JOIN localities_sys originloc ON originloc.id = flights.originloc
LEFT JOIN localities_sys destloc ON destloc.id = flights.destloc
LEFT JOIN aircraft ON aircraft.icao = flights.icao
WHERE pings.call = 'test';

edited Mar 29, 2020 at 12:31

answered Mar 29, 2020 at 12:27

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Damien Over a year ago

this is a postgres query not for MySql

Ankit Bajpai Over a year ago

@Damien, apologies for typo.

Collectives™ on Stack Overflow

Postgres Query Assistance Required

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related