MySQL select rows for which no related row exists for each date in a date range

Question

I have a query that selects from one table based on rows in a related table not existing for a particular date. Right now, I call this query several times once with each date I'm interested in. But for performance reasons, I'd like one round trip to the database. I want to expand this query to work over a date range and return all rows in the range where there are not related rows in the other table for each date (ie UNION of results of current query for each date in range). The results must include the date itself in each row. My current query is something like this:

   SELECT 
          t1.id,
          t1.field1,
          t1.field2, 
          '2016-03-17' AS nightof_date,
          1 as marker
        FROM 
          t1
        LEFT JOIN t2 ON (
          (t2.a_date = '2016-03-17') 
          AND (t2.t1_id=t1.id) 
          AND (some conditions...))
        WHERE 
          (t2.id is NULL)
          AND (some other conditions...)
        GROUP BY 
           t1.field3;

Here's a fiddle of a single query: http://sqlfiddle.com/#!9/553d49/6/0

And here's what I'm trying to achieve as a result for a start date 2016-03-17 and end-date 2016-03-20: http://sqlfiddle.com/#!9/553d49/6/0

(I suppose I could programmatically generate a monster union query like this but I was hoping for something a bit more elegant where I could just use the start and end dates)

Is there a way to do this without using the NUMBERS or similar trick (ie. I'm hoping not to have to generate a seperate table)?

You mean to say, now your query is working for a single date and you need to convert it into date range right? — Suganthan Madhavan Pillai, Mar 18 '16 at 06:45
Yea--I'm trying to avoid having to create a table, ie that falls under the "similiar trick" category. But maybe its the only way. — scotru, Mar 18 '16 at 07:02
No date on t1--technically there is a third table t3 related to t1 that has a different date range that restricts the data from t1 by requiring the date I'm looking at to be included in t3's range (ie. it's part of the 'other conditions' of the where clause)--but I'm hoping that doesn't really impact the question so I left it out for simplicity. — scotru, Mar 18 '16 at 07:14
It would be great if SO simply refused to publish SQL related questions without sample data and a desired result — Strawberry, Mar 18 '16 at 07:44
@Strawberry point taken. Here's a fiddle: http://sqlfiddle.com/#!9/553d49/2/0 I want the results from running this with 2016-03-17, 2016-03-18, 2016-03-19 together in one table (but for any arbitrary start and end date). — scotru, Mar 18 '16 at 08:04
There's still no clear desired result here. Under what circumstances would night_of be NULL ? — Strawberry, Mar 18 '16 at 09:42
You have to have some way to account for missing dates. If you're not going to use a utility table or a UNION then you can handle the logic of missing results in application code (actually my preference) — Strawberry, Mar 18 '16 at 09:52
@Strawberry The question also includes a result fiddle. The "missing results" are the only one's I'm interested in. I think a utility table may be the only way to accomplish this. — scotru, Mar 18 '16 at 15:45

Suganthan Madhavan Pillai · Answer 1 · 2016-03-18T07:57:25.763

0

Does this helps you ?

SELECT 
      t1.id,
      t1.name,
      t1.birth, 
      '2015-01-01 AND 2015-01-03' AS nightof_date
    FROM 
      t1
    LEFT JOIN t2 ON (t2.t1_id=t1.id
                    AND
                    (t2.birth BETWEEN '2015-01-01' AND '2015-01-03')) 
         WHERE (t2.id is NULL)
    GROUP BY 
       t1.name;

edited Mar 18 '16 at 07:57

answered Mar 18 '16 at 07:25

Suganthan Madhavan Pillai

5,495
9
51
84

Not really, see if this fiddle clarifies: http://sqlfiddle.com/#!9/553d49/2/0 I want the union of results for this query run with each date in a range. – scotru Mar 18 '16 at 08:05

MySQL select rows for which no related row exists for each date in a date range

1 Answers1