Search code examples
sqlpostgresqlsql-order-bylateral

Join on id or null and get first result

I have created the query below:

select * from store str
 left join(
  select * from schedule sdl 
  where day = 3  
  order by
   case when sdl.store_id is null then (
    case when sdl.strong is true then 0 else 2 end
   ) else 1 end, sdl.schedule_id desc
 ) ovr on (ovr.store_id = str.store_id OR ovr.store_id IS NULL)

Sample data: 

STORE
[store_id] [title]  
 20010      Shoes-Shop
 20330      Candy-Shop

[SCHEDULE]
[schedule_id] [store_id] [day] [strong] [some_other_data]
 1             20330      3     f        10% Discount
 2             NULL       3     t        0% Discount

What I want to get from the LEFT JOIN is either data for NULL store_id (global schedule entry - affects all store entries) OR the actual data for the given store_id.

Joining the query like this, returns results with the correct order, but for both NULL and store_id matches. It makes sense using the OR statement on join clause.

Expected results:

[store_id] [title]     [some_other_data]
 20010      Shoes-Shop  0% Discount
 20330      Candy-Shop  0% Discount

Current Results:

[store_id] [title]     [some_other_data]
 20010      Shoes-Shop  0% Discount
 20330      Candy-Shop  0% Discount
 20330      Candy-Shop  10% Discount

If there is a more elegant approach on the subject I would be glad to follow it.

Solution

  • DISTINCT ON should work just fine, as soon as you get ORDER BY right. Basically, matches with strong = TRUE in schedule have priority, then matches with store_id IS NOT NULL:

    SELECT DISTINCT ON (st.store_id)
       st.store_id, st.title, sl.some_other_data
FROM   store          st
LEFT   JOIN  schedule sl ON sl.day = 3
                       AND (sl.store_id = st.store_id OR sl.store_id IS NULL)
ORDER  BY NOT strong, store_id IS NULL;

    This works because:

    Basics for DISTINCT ON:

    Alternative with a LATERAL join (Postgres 9.3+):

    SELECT *
FROM   store st
LEFT   JOIN  LATERAL (
   SELECT some_other_data
   FROM   schedule
   WHERE  day = 3
   AND   (store_id = st.store_id OR store_id IS NULL)
   ORDER  BY NOT strong
        , store_id IS NULL
   LIMIT  1
   ) sl ON true;

    About LATERAL joins: