My SQL issue...
I have three tables:
titles = (id, title)
1, "Resident Evil: Afterlife"
2, "Is there an afterlife?"
3, "The Problem of Evil."
tokens = (id, token)
1, "resident"
2, "evil"
3, "afterlife"
4, "is"
5, "there"
6, "an"
7, "the"
8, "problem"
9, "of"
tokens_in_titles = (title_id, token_id, pos)
1, 1, 0
1, 2, 1
1, 3, 2
2, 4, 0
2, 5, 1
2, 6, 2
2, 3, 3
3, 7, 0
3, 8, 1
3, 9, 2
3, 2, 3
If you can't tell, a token is a group of alphanumeric characters. The
+ tokens_in_titles table describes which tokens are in which titles, a
+nd what position the tokens are in. This helps me quickly find title
+s with a particular set of tokens, or even tokens in a particular ord
+er, without doing in-string searches or dealing with mysql's fulltext
+ engine's limitations (word-length and stop-words).
I can very easily find titles with a particular token:
SELECT t.id, t.title
FROM titles t
JOIN tokens_in_titles tt ON t.id=tt.title_id
JOIN tokens k ON tt.token_id=k.id
WHERE k.token='evil'
GROUP BY t.id
But I have not figured out an efficient way, in a single query, to fin
+d titles that do NOT have a particular token.
Any advice?
|