0

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
mallix
  • 1,399
  • 1
  • 21
  • 44
  • Thetable definition (`\d tbl` in psql ) and your version of Postgres are core pieces of information for a question like this. And demo data that actually show cases what you want. – Erwin Brandstetter Apr 06 '15 at 02:06

2 Answers2

1

I think the easiest way to do what you want is to use distinct on. The question is then how you order it:

select distinct on (str.store_id) *
from store str left join
     schedule sdl 
     on (sdl.store_id = str.store_id or sdl.store_id is null) and dl.day = 3  
order by str.store_id,
         (case when sdl.store_id is null then 2 else 1 end)

This will return the store record if available, otherwise the schedule record that has a value of NULL. Note: your query has this notion of strength, but the question doesn't explain how to use it. This can be readily modified to include multiple levels of priorities.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Distinct on doesn t help. Let me explain. I have a set of stores with fixed discounts. Each store might have overrides set (schedule). Overrides have 3 priority levels: Global override strong (store_id NULL, strong TRUE), Normal Store override (store_id), Global override weak (store_id NULL, strong false) . So the same store might fall in to global and normal. If weak I choose normal, if strong I choose global. But my current query returns 3 records instead of 2. If I limit 1, I lose the global feature for the rest of the stores. – mallix Apr 05 '15 at 21:53
  • @mallix . . . `distinct on` should return one value per store, based on the prioritization set up in the `order by`. This seems like what you want to do. – Gordon Linoff Apr 06 '15 at 00:36
1

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:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228