Getting "Like" results from a Query 2

[fbacchus]

Hi:

I am sure that this question is not new but I could not find a reference in this forum, but here it is:

I have two tables (TABLE A and TABLE B). TABLE A contains a list if school names and TABLE B also contains school names and other school information. Unfortunately, TABLE A's school name may be different than TABLE B's school name. So I have to look up TABLE A's school name in TABLE B and find the matching school. I would manually set the value up in TABLE A to search TABLE B. What would the query look like ?

Note: Each individual value in TABLE A would need to check all the records in TABLE B to determine if there is a match.

Your assistance would be gladly appreciated. Thanks.


fb

 

[r937]

select tableA.name as Aname
     , iif(isnull(tableB.name)
           , 'no match' , tableB.name) as Bname
  from tableA
left outer
  join tableB
    on tableB.name = tableA.name

r937.com | rudy.ca

 

[fbacchus]

R937:

Thanks for the response. I am not sure if this will work, in my situation. I believe that I wasn't clear in my explaination. The name in Table A (or any part of the name) may appear in Table B. So by looking at the names in Table A and creating a list of possible hit for Table B may be my option. For example: Table A contains : "The MHT School for the advanced learning" while Table B's entry might be "The New York Manhattan sch for learning in advance" (this is a example). I will create a list with "advance" and need to check every record in Table B for the word "Advance".

So one entry in Table A will spin through the entire Table B to find a match before moving to the next record. I hope this helps.

thanks in advance.


fb

 

[r937]

I will create a list with "advance"

where is this list, in a separate table? do you intend to join to this list? one of both of TableA and TableB?

r937.com | rudy.ca

 

[fbacchus]

The list can be in a separate table (the prefered method) but I am afraid that the join will not work because of the differences between the two tables. I ran a query as an example and used the Like verb (like *advance*). The produced the expected result set but this may not be the way to go. What do you suggest ?

fb

 

[bboffin]

Assuming that you have set up a sensible value in tableA.Name you can use the LIKE operator in the join as follows:

select tableA.name as Aname
     , iif(isnull(tableB.name)
           , 'no match' , tableB.name) as Bname
  from tableA
left outer
  join tableB
    on tableB.name like "*" & tableA.name & "*"

This should produce the answers that you want but is entirely dependent on how well you have chosen the values in tableA.


Bob Boffin

 

[fbacchus]

Thanks. Appreciate it.

fb