CODEWITHSUNDEEP
sqlleft-joincasepostgispostgresql-9.3
TheRealJimShady
TheRealJimShady

Reputation: 917

Postgresql LEFT JOIN with CASE condition

I want to use a CASE condition in PostgreSQL, to decide which column of another table to join with. This is where I am and, I think, explains what I'm trying to do. Would appreciate your thoughts and ideas:

SELECT hybrid_location.*,
       concentration
FROM   hybrid_location
CASE   WHEN EXTRACT(month FROM hybrid_location.point_time) = 1 
            THEN LEFT JOIN (SELECT jan_conc FROM io_postcode_ratios) ON
            st_within(hybrid_location.the_geom, io_postcode_ratios.the_geom) = true
       WHEN EXTRACT(month FROM hybrid_location.point_time) = 2 
            THEN LEFT JOIN (SELECT feb_conc FROM io_postcode_ratios) ON
            st_within(hybrid_location.the_geom, io_postcode_ratios.the_geom) = true
       ELSE LEFT JOIN (SELECT march_conc FROM io_postcode_ratios) ON
            st_within(hybrid_location.the_geom, io_postcode_ratios.the_geom) = true
       END AS concentration;

Upvotes: 3

Views: 17717

Answers (1)

Mike T
Mike T

Reputation: 43642

This is a really unusual query that I don't think is valid. Even if conditional joins were valid, it would be difficult for a query planner to optimise. It can be rewritten to join to a single unioned table:

SELECT hybrid_location.*,
       concentration
FROM   hybrid_location
LEFT JOIN (
  SELECT 1 AS month_num, jan_conc AS concentration, io_postcode_ratios.the_geom
  FROM io_postcode_ratios
  UNION ALL
  SELECT 2 AS month_num, feb_conc AS concentration, io_postcode_ratios.the_geom
  FROM io_postcode_ratios
  UNION ALL
  SELECT 3 AS month_num, march_conc AS concentration, io_postcode_ratios.the_geom
  FROM io_postcode_ratios
) AS io_postcode_ratios ON
    EXTRACT(month FROM hybrid_location.point_time) = io_postcode_ratios.month_num
    AND ST_Within(hybrid_location.the_geom, io_postcode_ratios.the_geom)

Possibly a better way to organise the io_postcode_ratios table (if that is an option) is to normalise the monthly *_conc columns into one conc column with a date or month column. It will have more rows, but is easier to query from.

Upvotes: 6

Related Questions