Simple SELECT help needed 1

[leveetated]

Hello all,

SQL Server 2005. I have a query where I want to see if any of the values of one field in one table appear ANYWHERE in the field values of one field in another table.

Here's what I have so far:

select id, idnumber from table1
where cast(description as varchar) in (select sidnumber from table2)

This works, except that it's backward. It returns records where the ONLY data in table1.description = table2.sidnumber.

I want to find all the records where table2.sidnumber appears ANYwhere in table1.description. I've tried reversing the select without success.

Any help all very much appreciated!

Best,

Lee.

 

[jbenson001]

Can you post some sample data and expected results?

 

[leveetated]

Sure, thank you for the reply. It's not that the query is *wrong*, it's just that it's doing a literal field equals, instead of a "LIKE" operation.

For reference here's the half-way accurate query:

select id, idnumber from table1
where cast(description as varchar) in (select sidnumber from table2)

In other words, in the above result set, the description field strictly equals the sidnumber field, e.g.

description        sidnumber
33334              33334
20999              20999
65188              65188

What I want to see are results where sidnumber appears ANYwhere in the description field, e.g.

description        sidnumber
er33334abecid      33334
ww320999           20999
qq65188 899        65188

...I hope this explains it more clearly? Many thanks for any pointers.

 

[gmmastros]

try this:

Select *
From   Table1
       Inner Join Table2
         On Table1.Description Like '%' + Table2.sidnumber + '%'

This query will suffer performance problems if either table is large.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

Based on what the OP said, I think the ON needs to be reversed:

Select *
From   Table1
       Inner Join Table2
         On Table2.sidnumber LIKE '%' + Table1.Description + '%'

 

[leveetated]

*doh*! It really was that simple and I couldn't see the forest for the trees. Sigh.

Thanks gmmastros & all the best.

Lee.

 

[gmmastros]

I thought about that.... but in the sample data, the description has "more" than the sidnumber. Based on that, I think I got it right. But if not, switch an see.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

Ahhh yes you did, the sample data was reversed