Joining two tables on an NON exact match

[Damir Dezeljin]

Dear all.

I have two tables containing a Date column:
----
purchase_tbl:
date_clmn Date Short_Date
curr_clmn Number
other1_clmn Text
other2_clmn Text
...

change_tbl:
date_clmn Date Short_Date
curr_clmn Number
other3_clmn Text
...

currency_tbl:
curr_clmn Number
curr_text Text
----


The 'purchase_tbl' contains one or more daily records.

The 'currency_tbl' contains valid currencies.

The change_tbl contains exactly one record for each currency for each
month - the date is always the first day of the month (e.g. Apr 1 2005 /
EUR; Mar 1 2005 / EUR).

Now I would like to join 'purchase_tbl' and 'change_tbl' and so get
access to the change ration. There are basically two questions:
- How can I define TWO join criterias (date and currency)
- How can I 'ignore' a part of a certain field (day part of both
date_clmn should be ignored)

Thanks and best regards,
Dezo

 

[Brendan Reynolds]

Joining on two fields is simple enough ...

SELECT
FROM Table1 INNER JOIN Table2 ON (Table1.TestDate = Table2.TestDate) AND
(Table1.TestNumber = Table2.TestNumber);

There are various ways of matching on partial dates, here's one ...

SELECT *
FROM Table1 INNER JOIN Table2 ON ((Format$(Table1.TestDate, "mmyyyy") =
Format$(Table2.TestDate, "mmyyyy")) AND (Table1.TestNumber =
Table2.TestNumber));

This kind of thing can have performance implications where there are large
numbers of records. The database engine will not be able to use any index
that may exist on the date/time field.

 

[LGC]

If there are performance problems, you may want to add an additional long
integer field to each table. Set the value of the fields at record creation
to equal the number of months since some arbitrary date:

((Year(MyDate)-1900) * 12) + Month(MyDate)

Index these fields and use them in the relationship.

LGC