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?
 
tigin44Connect With a Mentor Commented:
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
 
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.