Link two tables using partial word match

Hi All,

I need to link two tables using partial word match.

How can I write a SQL statement to do so? (I am using MS-Access.)

Table One: [Table Name: tblStreet] [Field Name: Street]
123 ABC Street
124 ABC Street
125 ABC Street
1 XYZ AVE
2 XYZ AVE
3 XYZ AVE
10 CBS Street
11 CBS Street
12 CBS Street
100 Apple Road
101 Apple Road
102 Apple Road

Table Two: [Table Name: tblWord] [Field Name: Word]
ABC
CBS

The output should be:

123 ABC Street
124 ABC Street
125 ABC Street
10 CBS Street
11 CBS Street
12 CBS Street

Could this be done?

Thank you in advanced.

- Grasshopper -SQL Learner wrote:

Quote:

Originally Posted by

Hi All,
>
I need to link two tables using partial word match.
>
How can I write a SQL statement to do so? (I am using MS-Access.)
>
Table One: [Table Name: tblStreet] [Field Name: Street]
123 ABC Street
124 ABC Street
125 ABC Street
1 XYZ AVE
2 XYZ AVE
3 XYZ AVE
10 CBS Street
11 CBS Street
12 CBS Street
100 Apple Road
101 Apple Road
102 Apple Road
>
Table Two: [Table Name: tblWord] [Field Name: Word]
ABC
CBS
>
The output should be:
>
123 ABC Street
124 ABC Street
125 ABC Street
10 CBS Street
11 CBS Street
12 CBS Street
>
Could this be done?
>
Thank you in advanced.
>
- Grasshopper -

SELECT t.* FROM tblStreet t WHERE EXISTS(SELECT 1 FROM tblWord w WHERE
t.Street LIKE '%'+Word+'%')

--------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||Thank you so much Alex! It works. The only thing is that I replaced
the "%" with "*" since this is Transact SQL in Access. : )

By the way, is there any good book that I can use to learn SQL of this
kind?

- Grasshopper -