• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

ms sql query. Value of a field in one table included in a field in another table

I do this:
select * from table1 where field1 like '%john%'  or field1 like '%mike%'

For 2 or 3,4  values it is ok.

I need table2 with one field and n records

John
Mike
Fred
....
name_n
And  query that will work as the select I am using, except taking the field from table2

select * from table1 where field1 like '%john%'  or field1 like '%mike%'.... field1 like '%name_n%'

0
robrodp
Asked:
robrodp
  • 3
  • 2
2 Solutions
 
sdstuberCommented:
select * from table1 where exists (select null from table2 where table1.field1 like '%' + table2.field1 + '%')
0
 
QlemoC++ DeveloperCommented:
Something like this?
select distinct t1.* from table1 t1, table t2 where field1 like '%'+t2.field1+'%';

Open in new window

0
 
sdstuberCommented:
doesn't adding distinct potentially change the results?
0
 
QlemoC++ DeveloperCommented:
Yes, it sorts the result. The exists approach is probably performing better, anyway, as it will not require sort, only maintaining of an internal bitmap.
0
 
sdstuberCommented:
I meant actually eliminating data, not just the sorting.  but now that I think about it more, the only time that would happen would if table1 had duplicate rows. so it's likely fine for most real data.

I do agree though that the exists should be more efficient though
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now