Muliple Tables (Join Where)

[#1]

Here is how the data is setup:

Table 1 (OrderDate, Territory)
Table 2 (Territory, Staff, StartDate, EndDate)

I'm trying to pull in the staff into table one where the orderdate is within the startdate and enddate.

Any ideas on how to accomplish that?

[#2 · Accepted solution]

Oy - beyond hokey, but this could work:

TEMPTable:

load
OrderDate,
Territory
from table1

LEFT JOIN LOAD
Territory,
Staff,
StartDate,
EndDate
from table2;

TEMPTable2:
LOAD
Territory,
Staff,
IF(OrderDate>=StartDate and OrderDate<=EndDate, OrderDate) as OrderDate
RESIDENT TEMPTable;

FinalTable:
LOAD *
RESIDENT TEMPTAble2
WHERE NOT ISNULL(OrderDate);

DROP TABLE TEMPTable;
DROP TABLE TEMPTable2;

There must be a more elegant way to do it. I hate dealing with that many temp tables.

[#3]

Thanks Sally. That worked!

[johnw]

I think this (untested):

LEFT JOIN ([Table 1])
INTERVALMATCH (OrderDate,Territory)
LOAD
StartDate
,EndDate
,Territory
RESIDENT [Table 2]
;
LEFT JOIN ([Table 1])
LOAD *
RESIDENT [Table 2]
;
DROP TABLE
[Table 2]
;
DROP FIELDS
StartDate
,EndDate
;

Even if that's not exactly it, I think that's the basic idea. You use intervalmatch to match up the order date and territory to the start and end dates by territory. The first left join should, I think, put the start and end date on Table 1. At that point you have territory, start date and end date on table 1, so one more left join gets you the staff. After that, I assume you no longer need table 2 or the start and end dates.

[#5]

I agree with John's method.

But perhaps you should add DISTINCT in this place:

LEFT JOIN ([Table 1])
INTERVALMATCH (OrderDate,Territory)
LOAD DISTINCT
 StartDate
,EndDate
,Territory
RESIDENT [Table 2]
;

That way a each combination of Territory, StartDate and EndDate is unique. If each value in field Staff can only have one combination of those three fields, you don't need the DISTINCT of course.

[#6]

Aha. I knew there had to be a more elegant way of doing it.

[#7]

Thanks guys. I'll give this method a try.