By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,165 Members | 850 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,165 IT Pros & Developers. It's quick & easy.

Searching on more than one keyword

P: n/a
Hi

I have a client table. User provides four keywords and I need to select
clients whose names contain any two of the given keywords. How do I go about
doing this selection?

Thanks
Nov 26 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Sun, 26 Nov 2006 01:13:29 -0000, "John" <Jo**@nospam.infovis.co.uk>
wrote:

Intriguing question. OR would not work because it would pull any, not
any two.
I might consider doing this with rankings. Create a query like this:
SELECT Customers.CustomerID, Customers.CompanyName, 1 AS Weight
FROM Customers
WHERE (((Customers.CompanyName) Like [Param 1?]))
union all
SELECT Customers.CustomerID, Customers.CompanyName, 1 AS Weight
FROM Customers
WHERE (((Customers.CompanyName) Like [Param 2?]))
union all
SELECT Customers.CustomerID, Customers.CompanyName, 1 AS Weight
FROM Customers
WHERE (((Customers.CompanyName) Like [Param 3?]))
union all
SELECT Customers.CustomerID, Customers.CompanyName, 1 AS Weight
FROM Customers
WHERE (((Customers.CompanyName) Like [Param 4?]))

Then create a Totals query based on this one, with a Having clause
setting the Weight at >=2
You could even Order By DESC this column, thus giving the user a list
of hits, best ones first.

-Tom.
>Hi

I have a client table. User provides four keywords and I need to select
clients whose names contain any two of the given keywords. How do I go about
doing this selection?

Thanks
Nov 26 '06 #2

P: n/a
John wrote:
I have a client table. User provides four keywords and I need to select
clients whose names contain any two of the given keywords. How do I go about
doing this selection?
How about using the instr() function?

The following air SQL where clause assumes that 2 or more matches are
permitted. If only 2 are permitted, change the to an =

SELECT
Client_Name
FROM
Client_Table
WHERE
(iif(instr(1, Client_Name, Keyword_1)>0,1,0) +
iif(instr(1, Client_Name, Keyword_2)>0,1,0) +
iif(instr(1, Client_Name, Keyword_3)>0,1,0) +
iif(instr(1, Client_Name, Keyword_4)>0,1,0)) >2

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 26 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.