SQL Server Performance Forum – Threads Archive

Like operator through sub query

Hi, create table t1
(code varchar(5)) insert into t1(code)
values(‘abc’) insert into t1(code)
values(‘xyz’) create table t2
(id int, contents varchar(100)) insert into t2(id, contents)
values(1, ‘test content abc’) insert into t2(id, contents)
values(2, ‘test content def’) insert into t2(id, contents)
values(3, ‘test content xyz’) I want to retrieve data from table "t2" for the contents match with the
code data in t1 table, like I want to select the first and
third rows from t2 table. select * from t2 where contents like (select code from t1) The above sql is invalid, just for the understanding purpose I have
provided it. We have implemented the above requirement by iterating the data
from "t2" table and processed. Is it possible to achieve it in one select statement ? Thank you. Regards,
Deva

---

SELECT whatever
FROM t1
JOIN t2
ON t2.contents LIKE ‘%’+t1.code+’%’ But don’t expect overwehelming performance. You should rather normalise your structures.
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

---

Is your table normalised?
http://www.datamodel.org/NormalizationRules.html As said, if you use Like ‘%’+t1.code+’%’, index wont be used if any Madhivanan Failing to plan is Planning to fail

---

Hi, Thank you frank, this is what I wanted, thanks madhi for the data model URL. Regards,
Deva

---

But if possible, you have to redesign the table structure Madhivanan Failing to plan is Planning to fail

---

]]>