Searching for results where a record could appear more than once

I'm trying to make a search box more comprehensive. Currently it is a fairly simple search looking for the string to appear in a particular field. I now need to increase the scope but I just can't work out how.

I have these tables:
- Products
- Keywords
- Many-to-many linking the two so each product can have any number of keywords

I need the search box to search all the products and find all of them where the search phrase either exists in the product name or in any of the associated keywords.

Can someone point me in the right direction please?

Many thanks

Jon

you would need to edit the recordset to use a join query to return the related data from the keywords table.


se the following page for details on joined queries:
sql_join.asp

Thanks Jason.

I'm already using joins but the problem I have is it's returning the same record multiple times if more than one keyword matches it. I'm trying to do it so any match returns it but only returns it once.

Can that be done purely by the query with joins or do I need to put some scripting in too?

Cheers

Jon

add group by clause to your query:
sql_groupby.asp