Re: Keyword searching across 2 tables

There is a third solution. Have a table of keywords, have a table of user's keyswords and then join to the stock whenever a new stock is added.
Using the SQL Server demo database Northwind as a starting schema:

create table Keywords
(Keyword varchar(24) not null
, constraint Keywords_P primary key (Keyword )
)
go
create table CustomerKeywords
( CustomerID nchar(5) not null
, Keyword varchar(24) not null
, constraint CustomerKeywords_P primary key ( CustomerID , Keyword )
, constraint CustomerKeywords_U_1 unique ( Keyword , CustomerID )
, constraint Customer_F_CustomerKeywords foreign key (CustomerID)
 references Customers
, constraint Keywords_F_CustomerKeywords foreign key (Keyword)
references Keywords
)
go
insert into Keywords ( Keyword )
select 'MA' union all select 'BA'
go
insert into CustomerKeywords
( CustomerID , Keyword )

select 'NCnt2' , 'BA' union all
select 'NCnty' , 'MA' union all
select 'CACTU' , 'BA' union all
select 'CACTU' , 'MA'

select	CustomerKeywords.CustomerID
,	CustomerKeywords.Keyword
,	Employees.LastName, Employees.FirstName
from	CustomerKeywords
join	Employees
	on Employees.Notes like '% ' + Keyword + ' %'
	or Employees.Notes like Keyword + ' %'
	or Employees.Notes like '% ' + Keyword
	or Employees.Notes like Keyword