Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

japhy's scratchpad

by japhy (Canon)
on Jun 01, 2004 at 23:55 UTC ( #358848=scratchpad: print w/ replies, xml ) Need Help??

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?
Log In?
Username:
Password:

What's my password?
Create A New User
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (4)
As of 2014-09-20 22:07 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (163 votes), past polls