JOIN & WHERE

[#1]

Hi

Ive got the following 2 tables. Basically we have one key field that being the ItemID. I need to know in what Season a specific transaction took place based on the StartDate & EndDate in die Lookup Table.

So obviously i will need a JOIN on ItemID, but how do i take the SeasonDateStart & SeasonDateEnd into consideration when joining? With a regular join each row in the transaction table will join x3 against the Lookup table?

Any help will be much appreciated!

Regards

[Anonymous]

take the purchase date field and run an interval match with the start and end date... then you should be able to find out where the particular transaction fell on...

hope that helps..

[jagannalla]

Hi,

Use this code:

Transaction:

Load * from TransactionTable:

LookUp:

Load * from LookUpTable:

IntervalMatch ( PurchaseDate) select SessionStartDate, SessionEnd Date from Transaction;

Cheers!

Jagan

[#4]

I tried this, says a loop is formed between the ItemID fields, if i change the names,then it doesnt take the ItemID into consideration during the lookup?