qkrwngml
asked on
Can I Join two tables with Partially matched fields
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
Table1
ID, FieldA
===================
1 RestaurantPizzahut
2 PizzaHut
3 Pizzahut Rest.
Table2
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
Say, i have a table
Table1
ID, FieldA
===================
1 RestaurantPizzahut
2 PizzaHut
3 Pizzahut Rest.
Table2
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or you could build a column of portions of several columns to create a match key, would be sort of like a hash key
Regards,
Jim