Help required to phrase criteria in a JOIN

[Neeraj]

I am facing a problem relating to JOIN in query. I want to
JOIN two table based on the criteria that in the two JOIN
fields, the result should display only those records which
has atleast any 5 or more characters occuring in continous
stream anywhere in the string common in the two fields. eg

Table1 Table2
------ ------
ABCDEFGHIJ DEFGHIJKLMN

In this case the character stream "DEFGHI" is common in
the two columns. So the output should be the rows
satisfying this type of criteria.

Help & suggestions would be welcome.

Regards

Neeraj

 

[Michel Walsh]

Hi,


SELECT whatever
FROM table1, table2
WHERE myVBAfunction( table1.Field1, table2.Field2)


where the VBA function, public in a standard module, will scan for 5 or more
continuous equal characters, returning TRUE if so, FALSE otherwise. SQL
can't help much more with such a table design.


With a design table like

Table1
Charact, positn ' Fields name
a 1
b 2
c 3
.... ' data


and similar for Table2, then, untested, the following is likely to produce
the required result:


SELECT MIN(table1.positn), MIN(table2.positn)
FROM table1 INNER JOIN table2
ON table1.Charact = table2.Charact
GROUP BY table1.positn - table2.positn
HAVING COUNT(*) >=5



and assuming that at least one of the two tables have no duplicated values
under the field Charact... which is not verify in, as example, trying to
find matching sequence of gene. I'll come back on the general case, if I
find an elegant solution.





Hoping it may help,
Vanderghast, Access MVP