PostgreSQL Select value row depending on another value row How to select only the value of the column phase that corresponds to the value of the column type?, that way I want to show only the TAXI_SMALL values if the value of type is small_airport, or TAXI_LARGE If the value of type is large_airport as in a sample of my table:




ident
type
aircraft
phase
co2_lto_kg




LFPG
large_airport
AC300
APPROACH
2033.64


LFPG
large_airport
AC300
CLIMB OUT
3279.83


LFPG
large_airport
AC300
TAKE OFF
1281.1932


LFPG
large_airport
AC300
TAXI_LARGE
3675.672


LFPG
large_airport
AC300
TAXI_MEDIUM
2261.952


LFPG
large_airport
AC300
TAXI_SMALL
1413.72




The desired result:




ident
type
aircraft
phase
co2_lto_kg




LFPG
large_airport
AC300
APPROACH
2033.64


LFPG
large_airport
AC300
CLIMB OUT
3279.83


LFPG
large_airport
AC300
TAKE OFF
1281.1932


LFPG
large_airport
AC300
TAXI_LARGE
3675.672




This is the code I've so far and I know that the CASE instance is not at all right...
select 
ident, type, aircraft, phase,
avg(co2_by_phase_lto) as co2_lto_kg,
case(
    WHEN t.type ilike 'large'||'%' THEN phase('TAXI_LARGE')
)
from aviation.emissionsco2 as t
where t.aircraft ilike '%'||'300' and
t.rep_airp ilike '%'||'lfpg'
group by ident,phase
You can use NOT LIKE to include all the non taxi related data -- check that the phase doesn't begin with TAXI_. Then add for each size a comparison that couples the airport size to the taxi route with OR.
SELECT *
       FROM aviation.emissionsco2
       WHERE phase NOT LIKE 'TAXI$_%' ESCAPE '$'
              OR (phase,
                  type) IN (('TAXI_LARGE',
                             'large_airport'),
                            ('TAXI_MEDIUM',
                             'medium_airport'),
                            ('TAXI_SMALL',
                             'small_airport'));