Comparing column data in two tables

DBA's of SQL Server:

I have two tables, the columns of which I need to compare.

Table A
Col1............Col2
Name.......ID

ABC........ 1
DEF ........ 2
WXY.........3

Table B

Col1......Col2....Col3
Name1...Name2..Name3
A......... B.......C
D........ G........Z

I need to output every record of Table A where the even a single alphabet in name column matches with Table B.

So as in the above example there is a match for record 1 and record 2( D in DEF matches with record2 Name1 in Table B) in Table A but record3 (WXY) does not match with any of the alphabets of Table B. So I should get only the first two rows of Table A. Can anyone help me structure this query?

Appreciate your help.Try something like this

select * from tablea ta , tableb tb
where
ta.name like '%' + convert(varchar(1),tb.name1) +'%'
or
ta.name like '%' + convert(varchar(1),tb.name2) +'%'
or
ta.name like '%' + convert(varchar(1),tb.name3) +'%'

Originally posted by vivek_vdc
DBA's of SQL Server:

I have two tables, the columns of which I need to compare.

Table A
Col1............Col2
Name.......ID

ABC........ 1
DEF ........ 2
WXY.........3

Table B

Col1......Col2....Col3
Name1...Name2..Name3
A......... B.......C
D........ G........Z

I need to output every record of Table A where the even a single alphabet in name column matches with Table B.

So as in the above example there is a match for record 1 and record 2( D in DEF matches with record2 Name1 in Table B) in Table A but record3 (WXY) does not match with any of the alphabets of Table B. So I should get only the first two rows of Table A. Can anyone help me structure this query?

Appreciate your help.|||This doesn't work. Here is what happens. Let me make the tables simple.

Table A
Name......ID
B C A........1
D E F.........2
A............3

Table B

Name1
A

I need all records that contain alphabet A. With the query -

select * from tablea ta , tableb tb
where
ta.name like '%' + convert(varchar(1),tb.name1) +'%'

Only record 3 (A...3) is returned. I also need record 1. Note that the individual alphabets are separated by spaces.

Let me know. Thanks.

Originally posted by fhunth
Try something like this

select * from tablea ta , tableb tb
where
ta.name like '%' + convert(varchar(1),tb.name1) +'%'
or
ta.name like '%' + convert(varchar(1),tb.name2) +'%'
or
ta.name like '%' + convert(varchar(1),tb.name3) +'%'|||Here you go. I created two tables exactly as you specified:
create table table1
(Name varchar(3)
,ID int identity (1,1))
It has values:
ABC, 1
DEF, 2
WXY, 3

create table table2
(Name1 char(1)
,Name2 char(1)
,Name3 char(1))

It has values:
A, B, C
D, G, Z

Now run this:

select distinct table1.Name, table1.ID from table1, table2
where charindex (substring(NAME,1,1), Name1+Name2+Name3) <> 0
or charindex (substring(NAME,1,2), Name1+Name2+Name3) <> 0
or charindex (substring(NAME,1,3), Name1+Name2+Name3) <> 0