match ALL keywords search query
Posted: Fri Feb 09, 2007 2:55 pm
I am having trouble writing the SQL query for this situation:
I have two tables. One has a list of photos and the other has keywords for photos. Each photo can have multiple keywords.
photos
----------
photoID
photo_keywords
-------------------
photoID
keyword
When someone search for a single keyword, the query is easy. My problem is matching multiple keywords. Using the example "yellowstone river" I need to match photos that have both the keyword "yellowstone" and "river" or the keyword "yellowstone river".
Basically it's a match ALL keywords search (vs match ANY).
I have two tables. One has a list of photos and the other has keywords for photos. Each photo can have multiple keywords.
photos
----------
photoID
photo_keywords
-------------------
photoID
keyword
When someone search for a single keyword, the query is easy. My problem is matching multiple keywords. Using the example "yellowstone river" I need to match photos that have both the keyword "yellowstone" and "river" or the keyword "yellowstone river".
Basically it's a match ALL keywords search (vs match ANY).