Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
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 studying the Monastery: (12)
As of 2015-05-22 16:43 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    In my home, the TV remote control is ...









    Results (461 votes), past polls