how do I formulate this MYSQL SELECT query?
Posted on 2008-11-17
I have the following two tables(fields):
both fields are UNIQUE in the synset table, but neither field is UNIQUE in the sense table.
I want to SELECT all definitions whose synsetids are not associated with a specific wordid, like this:
SELECT synset.definition WHERE synset.synsetid = sense.synsetid AND ( sense.wordid != any value that is paired with a sense.synsetid value which is itself paired with wordid=1 in any row)
so for example, if a row (wordid,synsetid): (1,2) exists in the sense table, then the query would select no result from the sense table in which sense.synsetid=2 (regardless of the wordid value).
note that I can't just say "AND wordid != 1" because I also want to exclude rows where wordid != 1, but the synsetid is some value x such that there exists a row (1,x).
How do I turn that into SQL?