Join using LIKE

[JE]

I have two tables with data that is nearly exact - one table has truncated
data:

Table 1
BARRICK GOLD CORP
DIREXION SHS ETF TR

Table 2
BARRICK GOLD CORP COM ISIN#
DIREXION SHS ETF TR LARGE CAP BULL 3X SHS

I am wanting to join these two tables on the these fields to extract another
field. Is this possible? And is it possible without using VBA using a
Design View query?

Many thanks.

 

[Rick Brandt]

I have two tables with data that is nearly exact - one table has truncated
data:

Table 1
BARRICK GOLD CORP
DIREXION SHS ETF TR

Table 2
BARRICK GOLD CORP COM ISIN#
DIREXION SHS ETF TR LARGE CAP BULL 3X SHS

I am wanting to join these two tables on the these fields to extract
another
field. Is this possible? And is it possible without using VBA using a
Design View query?

It is possible and you do not need to use VBA, but you do have to use the
SQL view of the query designer. The graphical designer can only deal with
joins using =.

SELECT * FROM [Table 2]
INNER JOIN [Table 1]
ON [Table 2].FieldName LIKE [Table 1].FieldName & "*"

 

[Daryl S]

JE -

You can do this in query design. First, remove any links between the
tables, so they look like they are not joined at all.

Then, add the fields in the grid, and under the two fields from Table 2 put
criteria like this:
Like [Table 1]![field1] & "*"

Do this for both fields.

 

[KARL DEWEY]

If the short field is consistent in the number of characters you can use two
queries by creating a calculated field in the first and left function.
In the second query join on the calculated field.

 

[David W. Fenton]

You can do this in query design. First, remove any links between
the tables, so they look like they are not joined at all.

Then, add the fields in the grid, and under the two fields from
Table 2 put criteria like this:
Like [Table 1]![field1] & "*"

Do this for both fields.

Why do you need it under both fields? Certainly if you put that
criteria under Field1, all records will match, so it seems redundant
to me. It won't change the result set, but it might cause the query
optimizer to evaluate it incorrectly.

 

[John Spencer]

Given the scenario you might want to put
Like [Table 1][Field1] & "*" under [Table 2][Field1]

And on the next criteria put
Like [Table 2][Field1] & "*" under [Table 1][Field1]

If you were unsure of the direction of the match. One problem with this
approach is that if there is a null in field1 in either table then you are
going to end up matching every record (except those with nulls in field1).

There is a way around this, but the best solution is probably to use a
non-equi join as described elsewhere.

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

You can do this in query design. First, remove any links between
the tables, so they look like they are not joined at all.

Then, add the fields in the grid, and under the two fields from
Table 2 put criteria like this:
Like [Table 1]![field1] & "*"

Do this for both fields.

Why do you need it under both fields? Certainly if you put that
criteria under Field1, all records will match, so it seems redundant
to me. It won't change the result set, but it might cause the query
optimizer to evaluate it incorrectly.

 

[David W. Fenton]

Given the scenario you might want to put
Like [Table 1][Field1] & "*" under [Table 2][Field1]

And on the next criteria put
Like [Table 2][Field1] & "*" under [Table 1][Field1]

I think the original question was quite clear that the match was in
one direction, as it said:

I have two tables with data that is nearly exact - one table has
truncated data...

On the other hand, I was completely unable to decipher the sample
data provided in relation to that statement.

If you were unsure of the direction of the match. One problem
with this approach is that if there is a null in field1 in either
table then you are going to end up matching every record (except
those with nulls in field1).

There is a way around this, but the best solution is probably to
use a non-equi join as described elsewhere.

But a non-equi join works in only one direction, so the
corresponding implicit join using a WHERE clause would not be the
one with criteria on both fields, as you suggest.

 

[John Spencer]

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 & "*")

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

Given the scenario you might want to put
Like [Table 1][Field1] & "*" under [Table 2][Field1]

And on the next criteria put
Like [Table 2][Field1] & "*" under [Table 1][Field1]

I think the original question was quite clear that the match was in
one direction, as it said:

I have two tables with data that is nearly exact - one table has
truncated data...

On the other hand, I was completely unable to decipher the sample
data provided in relation to that statement.

If you were unsure of the direction of the match. One problem
with this approach is that if there is a null in field1 in either
table then you are going to end up matching every record (except
those with nulls in field1).

There is a way around this, but the best solution is probably to
use a non-equi join as described elsewhere.

But a non-equi join works in only one direction, so the
corresponding implicit join using a WHERE clause would not be the
one with criteria on both fields, as you suggest.

 

[John Spencer]

Ok. I had to try it and the query worked with the bi-directional join and
produced the expected results.

SELECT FAQ.fid, FAQ_BU.fid, FAQ.FSubject, FAQ_BU.fSubject
FROM FAQ INNER JOIN FAQ_BU
ON FAQ.fSubject LIKE FAQ_BU.fSubject & "*"
OR FAQ_BU.fSubject LIKE FAQ.fSubject & "*"
ORDER BY FAQ.FId, FAQ_BU.FID

If I wanted to get rid of directional duplicates I would need to add a where
clause. In this particular case
WHERE Faq.fid <= faq_BU.fid

Or
WHERE Faq.fid < faq_BU.fid if I wanted to eliminate matches where the fid
field matched and only end up with cases where the FID was different but the
Fsubject field was a match.

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

 

[David W. Fenton]

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.

 

[David W. Fenton]

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.

 

[John Spencer]

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