CODEWITHSUNDEEP
sqljointransactions
David Shields
David Shields

Reputation: 596

SQL where no match *for date range* in transaction table

Can't quite get my head into this: I want all those rows in master table that do not have matching transaction rows in transaction table, for a given date range. Example: master table m: 

id Name
1  John
2  David
3  Simon 
4  Jessica
5  Becky

transaction table t

id parent date       action
1  1      2015-08-28 IN
2  1      2015-09-03 IN
3  2      2015-08-17 IN
4  2      2015-10-01 IN
5  4      2015-09-05 IN

I want all those entries in m that do not have any transactions in september: so I should get m.id 2,3,5 

1 does not match: events in september
2 matches: events but none in  september
3 matches: no events at all
4 does not match: events in september
5 matches: No events at all

I can get those with nothing in t, with left join, and those with dates but not in range, with join, but can't see how to get both conditions. I might just be having a duh day.

Upvotes: 0

Views: 1213

Answers (2)

TommCatt
TommCatt

Reputation: 5636

Often, when we try to jump directly to a final query, it can turn out to be much more complicated that is should -- if it works at all. Generally, it doesn't hurt to just perform a straight join on the tables in question and look at the results. If nothing else, you verify the join is correct:

with
Master( ID, Name )as(
    select  1, 'John' from dual union all
    select  2, 'David' from dual union all
    select  3, 'Simon' from dual union all
    select  4, 'Jessica' from dual union all
    select  5, 'Becky' from dual
),
Trans( MasterID, EffDate, Action )as(
    select  1, date '2015-08-28', 'IN' from dual union all
    select  1, date '2015-09-03', 'IN' from dual union all
    select  2, date '2015-08-17', 'IN' from dual union all
    select  2, date '2015-10-01', 'IN' from dual union all
    select  4, date '2015-09-05', 'IN' from dual
)
select  *
from    Master  m
join    Trans   t
    on  t.MasterID  = m.ID;

(Excuse me for renaming some of your fields.) I happen to have Oracle up at the moment, you can use whatever you have. Probably this code will work with any non-Oracle system by just removing the 'from dual' from the CTE code.

Now let's extend the join criteria, but let's do so to generate the data we don't want to see.

join    Trans   t
    on  t.MasterID  = m.ID
    and t.EffDate >= date '2015-09-01'
    and t.EffDate < date '2015-10-01';

I've hard-coded the date values, but this is the best format to use to get "during the month of..." ranges. Every value is selected from the first tick of the first day of the month to the absolutely last tick before the first day of the next month. You'll want to store these values in variables or generate them on the fly, of course.

So now we see only the two transactions that occurred during September. The obvious next step is to perform an outer join so we get all the other Master records that don't match.

left join Trans   t

Now we have all the records we want, plus the two that we don't want. As they are the only ones that match the date restrictions, we add filtering criteria to remove those. Here is the final query:

select  m.*
from    Master  m
left join Trans   t
    on  t.MasterID  = m.ID
    and t.EffDate >= date '2015-09-01'
    and t.EffDate < date '2015-10-01'
where   t.MasterID is null;

Simple really. Once you've done this a few times, you'll be able to jump to the finished query without the intervening steps. Still, it doesn't hurt to execute the intervening steps and look at the outputs along the way. Any flaws in the logic will be caught earlier when it can be fixed easier.

Upvotes: 1

A ツ
A ツ

Reputation: 1267

select all master, join with transaction grouped by parent (which results in 0..1 row per master entry).

if there is no transaction record, t.parent will be null which translates in no transaction for that master entry.

if there are transaction entries, you'll find the count in t.a, if some of them are in september, you'll find them in t.m9

if you want all master without any transaction, you'll filter where t.parent is null

if you want all master without a transaction in september, you'll filter where t.parent is null or t.m9=0

select m.id, m.name, 
     , t.a, t.m9 
from master_table m
left join ( select a  = count(*)
                 , m9 = count(case when datepart(Month, t.date) = 9 then 1 end)
                 , t.parent 
            from transaction_table t 
            group by t.parent
          ) t on t.parent = m.id
where t.parent is null or t.m9=0

Upvotes: 0

Related Questions