Joining two tables and WHERE

[gardan]

I have two tables:

Table1:
Date, OriginID,DestID
2/1/2010,3345,USFF
3/5/2009,4453,USXX
2/23/2010,2123,USCC

Table2:
OriginID,Value,ValidOn,ValidTo
3345,dddd,12/31/2009,4/30/2010
3345,ffff,1/1/2009,12/30/2009
4453,dddd,12/31/2009,4/30/2010
4453,ffff,1/1/2009,12/30/2009
2123,dddd,12/31/2009,4/30/2010
2123,ffff,1/1/2009,12/30/2009

I want the end set look like this:
TableEND:
Date, OriginID,DestID,Value
2/1/2010,3345,USFF, dddd
3/5/2009,4453,USXX,ffff
2/23/2010,2123,USCC,dddd

I need make a WHERE statement where i look for Table1.Date field between Table2.ValidOn and Table2.ValidTo.

When I try to join them together then use a resident to filter out what i don't need, i run into memory issues. In reality Table1 and Table2 are pretty big. I tried to join them with a KEEP statement, but again, you can't really use the fields from table1 to filter on what you add in from table2

[#2 · Accepted solution]

Something like this:

Table1:
load * inline [
Date, OriginID,DestID
2/1/2010,3345,USFF
3/5/2009,4453,USXX
2/23/2010,2123,USCC
];

Table2:
load * inline [
OriginID,Value,ValidOn,ValidTo
3345,dddd,12/31/2009,4/30/2010
3345,ffff,1/1/2009,12/30/2009
4453,dddd,12/31/2009,4/30/2010
4453,ffff,1/1/2009,12/30/2009
2123,dddd,12/31/2009,4/30/2010
2123,ffff,1/1/2009,12/30/2009
];

Left join (Table2)
intervalmatch (Date, OriginID)
load ValidOn, ValidTo, OriginID
resident Table2;

left join (Table1)
load OriginID, Date, Value
resident Table2;

drop table Table2;

[gardan]

Thanks for the help!