Can I Join two tables with Partially matched fields

Posted on 2008-11-16
Medium Priority
Last Modified: 2012-05-05
I think there are ways to have a Join two table with partially matching fields of each table. But Only exact match were found in the help. Please Help me

Say, i have a table

ID, FieldA
1    RestaurantPizzahut
2    PizzaHut
3    Pizzahut Rest.

Categ     Field
AA-bbb    PizzaHut
AA-ccc    SteakHouse
AA-ddd    CentralMeal

Than I wanto have a resulting table like this

id   AddrCode    Field
1    AA-bbb      PizzaHut
2    AA-bbb      PizzaHut
3    AA-bbb      PizzaHut

Direct Query Statement For Access(SQL) is fine.
or Direction via UI is also o.k

Thanks in advance
Question by:qkrwngml
LVL 93

Accepted Solution

Patrick Matthews earned 1000 total points
ID: 22973015
You can do joins that are simply based on equality.  In this case, you could try something like:

SELECT t1.ID, t2.Categ, t2.[Field]
    Table2 t2 ON t1.FieldA Like "*" & t2.[Field] & "*"
LVL 18

Expert Comment

ID: 22973087
Hello qkrwngml,

Or you could build a column of portions of several columns to create a match key, would be sort of like a hash key


LVL 17

Expert Comment

ID: 22973089
A more accurate/readable query is like:
SELECT t1.ID, t2.[Categ] AS AddrCode, t1.[FieldA]
FROM [Table1] t1, [Table2] t2
WHERE t1.[FieldA] LIKE '%PizzaHut%' AND t2.[Field] = 'PizzaHut';

In SQL Server wild card character maybe * instead of %. You can write satndard/recommended ANSI join syntax like mathewspatrick which helps in filtering records in FROM clause which executes first and then further filtering in WHERE clause.

LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 total points
ID: 22973145
try this

select Table1.ID, Table2.Categ as AddCode, Table2.FieldA
from Table1, Table2
where (instr([table1].[fieldA],[table2].[FieldA]))>0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question