Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
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 surveying the Monastery: (10)
As of 2014-11-26 23:18 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My preferred Perl binaries come from:














    Results (176 votes), past polls