I want to formulate a query using the following tables:
table user_words, containing fields user_id, word_id
table word_tags, containing fields word_id, tag_id
I will use the following constant variables in the query:
I want the query to select word_ids based on the following criteria:
For each selected word_id, there exists in the word_tags table a record with that word_id and a tag_id that equals one each of $tag_id1, $tag_id2, and $tag_id3.
For each selected word_id, there exists a user_id such that the record $requesting_user_id/word_id exists in user_words
So far I have this query, which I expect should work if I am using only $tag_id1 and not $tag_id2 or $tag_id3:
SELECT user_words.word_id FROM user_words, word_tags WHERE word_tags.tag_id='$tag_id1' AND word_tags.word_id=user_words.word_id AND user_words.user_id='$requesting_user_id'
I want to extend this query so that it returns only those records that also satisfy the $tag_id2 and $tag_id3 criteria. How do I do this?