how do I formulate this MYSQL SELECT query?

I have the following two tables(fields):

synset(synsetid,definition)
sense(wordid,synsetid)

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?
bitt3nAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tigin44Commented:
this will give you what you want

SELECT wordid, synsetid
FROM sense S
WHERE NOT EXISTS (SELECT synsetid FROM synset P WHERE S.synsetid = P.synsetid)

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bitt3nAuthor Commented:
cool.. I made the following change to add the wordid requirement and it works fine (really slow but I think I need to add some indexes)

SELECT wordid, synsetid
FROM sense S
WHERE NOT EXISTS (SELECT synsetid FROM synset P WHERE S.synsetid = P.synsetid AND P.wordid=1)

is it possible to use a similar kind of subquery to solve the problem I am having in the below question?

http://www.experts-exchange.com/Database/MySQL/Q_23909254.html
0
tigin44Commented:
sure you can...
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

bitt3nAuthor Commented:
ok cool if you want to post a solution there I'd be happy to give you the points for it. The guy who answered claims it's not possible.
0
bitt3nAuthor Commented:
thanks!!
0
bitt3nAuthor Commented:
I tried creating a subquery in the same way for that query, but I'm getting a different result for some reason. If you could offer me any advice on that question that would be excellent.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.