Can I Join two tables with Partially matched fields

Posted on 2008-11-16
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 92

    Accepted Solution

    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]
    FROM Table1 t1 INNER JOIN
        Table2 t2 ON t1.FieldA Like "*" & t2.[Field] & "*"
    LVL 18

    Expert Comment

    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

    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 119

    Assisted Solution

    by:Rey Obrero
    try this

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

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    MySQL 11 48
    query Syntax 4 21
    calculated field in a table? 3 20
    Change format of subform 3 13
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now