SQL experts:

I am comparing columns of two tables with a LIKE clause. I am not getting the desired result. Please help.
Here is the example.

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


Table B

Name1
A

I need all records of Table A that contain alphabet A from Table B. With the query -

select * from tablea ta , tableb tb
where
ta.name like '%' +tb.name1+'%'

Only record 3 (A....3) is returned. I also need record 1 as it contains alphabet A(B C A). Note that the individual alphabets are separated by spaces.

Let me know. Thanks.

i tried out with the following

TAB1
======
Id, Col1
----------
1, A
2, B C
3, A B
4, D E
5, F A G

TAB2
======
Col1
-----
A

this is the result
SQL> SELECT * FROM TAB1, TAB2
2 WHERE TAB1.col1 LIKE '%'||TAB2.col1||'%'
3 /

ID COL1 COL1
---------- ---------- ----------
1 A A
3 A B A
5 F A G A

Elapsed: 00:00:00.00
SQL>

looks like its working fine

This is not working. It gives me incorrect syntax near |. I am executing the query in SQL Server Query Analyzer.

oops!

this works on Oracle. about SQL Server, no idea.

but the logic should be the same

sql server string concatenation is non-standard, it uses the plus sign instead of double pipes

select * from tab1, tab2
where tab1.col1 like '%'+tab2.col1+'%'