[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how do I formulate this MYSQL SELECT query?

Posted on 2008-11-17
6
Medium Priority
?
274 Views
Last Modified: 2012-05-05
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?
0
Comment
Question by:bitt3n
  • 4
  • 2
6 Comments
 
LVL 26

Accepted Solution

by:
tigin44 earned 2000 total points
ID: 22979659
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
 

Author Comment

by:bitt3n
ID: 22980080
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
 
LVL 26

Expert Comment

by:tigin44
ID: 22980177
sure you can...
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:bitt3n
ID: 22981173
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
 

Author Closing Comment

by:bitt3n
ID: 31517618
thanks!!
0
 

Author Comment

by:bitt3n
ID: 22981653
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question