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

Command TExt

How do I best get the "entire" text of the commands being executed on my SQL
2000 server?
I would like to query the database tables or schema views, not run a trace.
Again, Profiler is not an option.
cmd column from master..sysprocesses does not provide, dbcc
inputbuffer(spid) returns a truncated value...
My goal is to have a vb client query sysprocesses for all "running"
processes, then for each running spid - what is the command it is executing...
TIA,
Chris
The only way is through a trace job. Sysprocesses, dbcc inputbuffer, or
even fn_get_sql() doesn't have a large enough buffer to show all large
commands.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:CD692AD8-2C0C-4475-9681-700CD03F4334@.microsoft.com...
> How do I best get the "entire" text of the commands being executed on my
> SQL
> 2000 server?
> I would like to query the database tables or schema views, not run a
> trace.
> Again, Profiler is not an option.
> cmd column from master..sysprocesses does not provide, dbcc
> inputbuffer(spid) returns a truncated value...
> My goal is to have a vb client query sysprocesses for all "running"
> processes, then for each running spid - what is the command it is
> executing...
> TIA,
> Chris
|||fn_get_sql() should be pretty close to get what you want (cached ones
anyway).
Linchi
"Danny" <someone@.nowhere.com> wrote in message
news:Co_2f.17216$at1.15123@.trnddc05...
> The only way is through a trace job. Sysprocesses, dbcc inputbuffer, or
> even fn_get_sql() doesn't have a large enough buffer to show all large
> commands.
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:CD692AD8-2C0C-4475-9681-700CD03F4334@.microsoft.com...
>
|||Hi,
SQL Profilor is ur last arm u can use.C2 trace level can do this for u
but i dont weather is it posible to trasfer that in a table.
from
Doller
Danny wrote:[vbcol=seagreen]
> The only way is through a trace job. Sysprocesses, dbcc inputbuffer, or
> even fn_get_sql() doesn't have a large enough buffer to show all large
> commands.
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:CD692AD8-2C0C-4475-9681-700CD03F4334@.microsoft.com...

Command TExt

How do I best get the "entire" text of the commands being executed on my SQL
2000 server?
I would like to query the database tables or schema views, not run a trace.
Again, Profiler is not an option.
cmd column from master..sysprocesses does not provide, dbcc
inputbuffer(spid) returns a truncated value...
My goal is to have a vb client query sysprocesses for all "running"
processes, then for each running spid - what is the command it is executing.
.
TIA,
ChrisThe only way is through a trace job. Sysprocesses, dbcc inputbuffer, or
even fn_get_sql() doesn't have a large enough buffer to show all large
commands.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:CD692AD8-2C0C-4475-9681-700CD03F4334@.microsoft.com...
> How do I best get the "entire" text of the commands being executed on my
> SQL
> 2000 server?
> I would like to query the database tables or schema views, not run a
> trace.
> Again, Profiler is not an option.
> cmd column from master..sysprocesses does not provide, dbcc
> inputbuffer(spid) returns a truncated value...
> My goal is to have a vb client query sysprocesses for all "running"
> processes, then for each running spid - what is the command it is
> executing...
> TIA,
> Chris|||fn_get_sql() should be pretty close to get what you want (cached ones
anyway).
Linchi
"Danny" <someone@.nowhere.com> wrote in message
news:Co_2f.17216$at1.15123@.trnddc05...
> The only way is through a trace job. Sysprocesses, dbcc inputbuffer, or
> even fn_get_sql() doesn't have a large enough buffer to show all large
> commands.
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:CD692AD8-2C0C-4475-9681-700CD03F4334@.microsoft.com...
>|||Hi,
SQL Profilor is ur last arm u can use.C2 trace level can do this for u
but i dont weather is it posible to trasfer that in a table.
from
Doller
Danny wrote:[vbcol=seagreen]
> The only way is through a trace job. Sysprocesses, dbcc inputbuffer, or
> even fn_get_sql() doesn't have a large enough buffer to show all large
> commands.
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:CD692AD8-2C0C-4475-9681-700CD03F4334@.microsoft.com...