Link two tables using partial word match

[SQL Learner]

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 -

[Alex Kuznetsov]

SQL Learner wrote:

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/

[SQL Learner]

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 -