Join using LIKE

John Spencer wrote in
:

FROM FAQ INNER JOIN FAQ_BU
ON FAQ.fSubject LIKE FAQ_BU.fSubject & "*"
OR FAQ_BU.fSubject LIKE FAQ.fSubject & "*"

I was under the impression that have a join statement with the
tables in opposite order in the conditions doesn't work. I'm too
tired to test it, though.

I still can't quite figure out the utility of such a thing, though.
It certainly doesn't fit the original poster's scenario, which was
one that makes perfect sense to me.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

It does work. And yes we have gone far astray of the original poster's
request. I think we can terminate this discussion.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

David W. Fenton wrote:
John Spencer wrote in
:

I don't disagree. I was only pointing out a possible reason for
testing both directions.

I'm not sure that you could not use a non-equi join in both
directions.

I would have to test whether or not this would work - don't have
the time right now. Hopefully, I will have some time tomorrow to
satisfy my curiousity.

SELECT *
FROM Table1 INNER JOIN Table2
ON (Table1.Field1 Like Table2.Field1 & "*"
OR Table2.Field1 Like Table1.Field1 & "*")

That would throw an error, because the tables have to be in the same
order in a Join statement. You could only do it with a second
instance of one of the tables.

And it's not what the original poster asked for, since it was made
quite clear that the field in one table was a truncated version of
the data in the same field in the other table.