Question 90099 in PostgreSQL Question posted in
The official documentation can be found here.

case expression inside this left join? – Postgresql

so I have this left join

LEFT JOIN LATERAL (SELECT d.country FROM db.patient_info d
           WHERE  d.id IN (SELECT DISTINCT st.category FROM db.surgery_types st, db.surgery_record sr
               WHERE sr.id = st.surgery_record_id AND sr.surgery_type_id = m.id)
           ORDER BY d.priority, d.country
           LIMIT 1
           
         ) c ON TRUE

the issue is that sometimes d.country comes back null. How can I add a case statement in the left join so that when d.country IS NULL then ‘USA’?

My results look like this

Patient Name Surgery Type
Dave USA
Richard EU
Ben EU
Sally JP
Bob null
Dicky null

I want to modify the left join so that it looks more like this

Patient Name Surgery Type
Dave USA
Richard EU
Ben EU
Sally JP
Bob USA
Dicky USA

Thoughts?

Tags:

2

Answers


  1. 0

    Use coalesce which returns the first non-null value.

    -- I have no idea if this lateral join is valid.
LEFT JOIN LATERAL (
  SELECT coalesce(d.country, 'USA')
  FROM db.patient_info d
  WHERE  d.id IN (
    SELECT DISTINCT st.category
    FROM db.surgery_types st, db.surgery_record sr
    WHERE sr.id = st.surgery_record_id AND sr.surgery_type_id = m.id
  )
  ORDER BY d.priority, d.country
  LIMIT 1         
) c ON TRUE

    Though the order by will still use null so it might not sort properly. You might want to split this into a CTE.

    -- Again, no idea if the lateral join is valid,
-- just showing a technique.
with countries as(
  SELECT coalesce(d.country, 'USA') as country
  FROM db.patient_info d
  WHERE  d.id IN (
    SELECT DISTINCT st.category
    FROM db.surgery_types st
    JOIN db.surgery_record sr ON sr.id = st.surgery_record_id
    -- Don't know what m is
    WHERE sr.surgery_type_id = m.id
  )
)
with first_country as (
  select country
  from countries
  order by priority, country
  limit 1
)
select
...
LEFT JOIN LATERAL countries on true

    Finally, it might be simpler and faster to update the table to set all null countries to USA, and then make the column not null.

    Login or Signup to reply.
  2. 0

    Not looking into your business logic and whether a lateral join is needed at all or a scalar subquery in the select list of expressions would be enough, here is my suggestion.

    CROSS JOIN LATERAL 
(
 select coalesce
 (
   ( /* your lateral subquery in the brackets here */),
   'USA'
 ) as country
) as c

    You do not need left join anymore. Please note that this will only work if the subquery is scalar.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search