Query - need help using the IN function/statement

Hello all,
I am trying to find the instances in a field containg specific keywords or
strings of information. My table name is History, and my field name is
Notes. So what I am trying to do is find every record where History.Notess
conatins;
'chrom' or 'cell' or 'lab'
I think I need the IN functin as opposed to using a bunch of OR statements.
The OR statments work, but there are so many different keywords/strings,
that it is a real mess to enter all of the information.
Thank you for your help
JohnJohn,
You can put all the keywords in a table variable / temporary table /
permanent table a use:
select distinct notes
from history as h inner join t1 on h.notes like '%' + t1.keyword + '%'
Example:
use northwind
go
create table t1 (
c1 varchar(255)
)
go
create table t2 (
keyword varchar(25) not null unique
)
go
insert into t1 values('microsoft')
insert into t1 values('oracle')
insert into t1 values('microfocus')
go
insert into t2 values('micro')
insert into t2 values('of')
go
select distinct
t1.c1
from
t1
inner join
t2
on t1.c1 like '%' + t2.keyword + '%'
go
drop table t1, t2
go
Column [notes] can not be of type text / ntext.
AMB
"John Lloyd" wrote:

> Hello all,
> I am trying to find the instances in a field containg specific keywords or
> strings of information. My table name is History, and my field name is
> Notes. So what I am trying to do is find every record where History.Notes
s
> conatins;
> 'chrom' or 'cell' or 'lab'
> I think I need the IN functin as opposed to using a bunch of OR statements
.
> The OR statments work, but there are so many different keywords/strings,
> that it is a real mess to enter all of the information.
> Thank you for your help
> John
>sql

Queries in productions AND history tables

I've got a large and growing database in SQL Server 7.0. I'd like to utilize a monthly stored procedure that will search various tables for records that are older than 3 months and copy this data out to corresponding history tables. Typically, most production queries are run only on the new data, but occasionally (like at year-end, for example), we will need to run some queries on data extending back 12 months -- that is, on data in the production tables AND in the history tables. Notwithstanding the fact that I've never done stored procedures*, I would like to know if it is possible to run a query that can search for data in both production and history tables at the same time. I know this sounds like a stupid question, but I read somewhere that doing this qould require some kind of Joining function that is complex...?

Thanks,
Whill96205

* Also, any good resources I could use for developing stored procedures?Well that would be a VIEW with a union

And here's a fundamental question...do you have an AD_DT column in all your tables

How big is the database?|||UNION queries can be a little bit tricky, but are not too difficult if both the tables you are pulling from have the same structure. Don't be intimidated.

Select [Column1], [Column2], [Column3]...from CurrentTable
UNION
Select [Column1], [Column2], [Column3]...from HistoryTable

Not so tough, eh?|||Thanks Brett and Blindman. I guess there's no big mystery for unioning then. :)

Well that would be a VIEW with a union

And here's a fundamental question...do you have an AD_DT column in all your tables

How big is the database?

Um... No. What is an AD_DT column?

--whill

P.S. btw, I'm using SQL Server 2000, not 7.0, if that makes any difference.|||Oh - almost forgot. :rolleyes: Regarding database size... The database currently has approximately 43000 records (transactions) in it, increasing by about 100 per day. My task is to incorporate a bunch of related data which the client is currently tracking in several huge spreadsheets. This may nearly double the amount of data, but not necessarily the number of transactions. So I am redesigning the db table structure to better accomdate and utilize the new data that will be imported from the spreadsheets.