Multiple search parameter query

[mark_downes]

I have 3 tables like the following:

TableName: Keywords KeywordsToObjects Objects
Fields: KeywordID Name KeywordID ObjectID ObjectID
Data: 1 Animal 1 80 80
2 Dog 2 80 81
3 Cat 1 81
3 81

Thus object 80 is associated with the keywords Animal and Dog
object 81 is associated with the keywords Animal and Cat

How do I write a query to give me back all objects which match the
following keywords:
(Animal OR Dog) AND (Cat)
which should give back all fields for object 81?

 

[Allen Browne]

Time to learn about subqueries.

You will end up with something like this:

SELECT Objects.ObjectID
FROM Objects
WHERE EXISTS (SELECT ObjectID
FROM KeywordsToObjects INNER JOIN Keywords
ON KeywordsToObjects.KeywordID = Keywords.KeywordID
WHERE (KeywordsToObjects.ObjectID = Objects.ObjectID)
AND (Keywords.[Name] IN ("Animal", "Dog")))
AND EXISTS (...

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[mark_downes]

Thanks Allen, but exactly where at http://allenbrowne.com/tips.html?
Can you give me a more specific URL?

 

[Allen Browne]

You were not able to get to the knowledgebase article on Microsoft's site?

It is a reasonable introduction to the idea of subqueries.

The fact is that the Dog, Cat, and Animal keywords are in different records
in the junction table (KeywordsToObjects), and so your main query has to ask
these questions independently to see if such a record exists for each one.
Subqueries are probably the simplest way to do that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Time to learn about subqueries.

You will end up with something like this:

SELECT Objects.ObjectID
FROM Objects
WHERE EXISTS (SELECT ObjectID
FROM KeywordsToObjects INNER JOIN Keywords
ON KeywordsToObjects.KeywordID = Keywords.KeywordID
WHERE (KeywordsToObjects.ObjectID = Objects.ObjectID)
AND (Keywords.[Name] IN ("Animal", "Dog")))
AND EXISTS (...

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

I have 3 tables like the following:

TableName: Keywords KeywordsToObjects Objects
Fields: KeywordID Name KeywordID ObjectID ObjectID
Data: 1 Animal 1 80 80
2 Dog 2 80 81
3 Cat 1 81
3 81

Thus object 80 is associated with the keywords Animal and Dog
object 81 is associated with the keywords Animal and Cat

How do I write a query to give me back all objects which match the
following keywords:
(Animal OR Dog) AND (Cat)
which should give back all fields for object 81?

 

[mark_downes]

Thanks Allen. It works great!