Join using LIKE
in [Getting Started]
|
Prev: How do I retrieve my ID code.I have already registered.
Next: Having problems understanding sub-form selections and uses ofcomb
From: John Spencer on 22 Feb 2010 19:21 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 David W. Fenton wrote: > =?Utf-8?B?RGFyeWwgUw==?= <DarylS(a)discussions.microsoft.com> wrote in > news:FE1ABC91-992C-46EB-A0DB-49AF6D16185F(a)microsoft.com: > >> 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. >
From: David W. Fenton on 23 Feb 2010 17:34 John Spencer <spencer(a)chpdm.edu> wrote in news:#Di5e4BtKHA.3360(a)TK2MSFTNGP06.phx.gbl: > 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. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: John Spencer on 23 Feb 2010 20:46 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 David W. Fenton wrote: > John Spencer <spencer(a)chpdm.edu> wrote in > news:#Di5e4BtKHA.3360(a)TK2MSFTNGP06.phx.gbl: > >> 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. >
From: John Spencer on 24 Feb 2010 08:54 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 John Spencer wrote: > 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
From: David W. Fenton on 24 Feb 2010 23:09
John Spencer <spencer(a)chpdm.edu> wrote in news:eEeZ#MPtKHA.3904(a)TK2MSFTNGP02.phx.gbl: > 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 http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |