?
Solved

How to `EXCEPT' in MySQL?

Posted on 2009-12-22
13
Medium Priority
?
368 Views
Last Modified: 2012-06-27
I have the following query:

SELECT FNAME, LNAME
FROME  EMPLOYEE
WHERE NOT EXISTS
(
    (SELECT PNUMBER
     FROM PROJECT
     WHERE DNUM = 5)
EXCEPT
    (SELECT PNO
     FROM WORKS_ON
     WHERE SSN=ESSN)
);

Please help me convert this query into valid MySQL syntax.

I've converted the query into:

SELECT FNAME, LNAME
FROM EMPLOYEE A
WHERE NOT EXISTS
(
SELECT *
FROM WORKS_ON B
WHERE
(B.PNO IN    (SELECT PNUMBER
              FROM PROJECT
              WHERE DNUM = 5))
AND NOT EXISTS
              (SELECT *
              FROM WORKS_ON C
              WHERE C.SSN = A.SSN
              AND C.PNO = B.PNO)
);

but the returning result is blank while the exact result is 4.

Thank you!
0
Comment
Question by:dienbaquan2nd
  • 6
  • 4
  • 3
13 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26104427
what about:

SELECT FNAME, LNAME
FROME  EMPLOYEE
WHERE NOT EXISTS
(
    SELECT PNUMBER
     FROM PROJECT
     WHERE DNUM = 5)
OR EXISTS
    (SELECT PNO
     FROM WORKS_ON
     WHERE SSN=ESSN)
;
0
 

Author Comment

by:dienbaquan2nd
ID: 26104693
blank too! :(
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26104814
can you please show sample data
and clarify the data types?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 41

Expert Comment

by:Sharath
ID: 26105211
try this
SELECT FNAME, LNAME
FROME  EMPLOYEE
WHERE NOT EXISTS
(
    SELECT PNUMBER
     FROM PROJECT
     WHERE DNUM = 5 AND PNUMBER NOT IN (

    SELECT PNO
     FROM WORKS_ON
     WHERE SSN=ESSN)
);

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 26105226
extra 'E' in FROM
SELECT FNAME, LNAME
FROM  EMPLOYEE
WHERE NOT EXISTS
(
    SELECT PNUMBER
     FROM PROJECT
     WHERE DNUM = 5 AND PNUMBER NOT IN (

    SELECT PNO
     FROM WORKS_ON
     WHERE SSN=ESSN)
);

Open in new window

0
 

Author Comment

by:dienbaquan2nd
ID: 26108497
Blank again.

Please, just show me how to stimulate the EXCEPT/MINUS in MySQL.

Thank you!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26108541
the queries should actually work, and do the "except/minus".
so, we must know the data you are working with ...
are you 200% sure you actually have data?
can you run the different parts of the sql, to see if they work individually?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 26108563
what about this?

SELECT FNAME, LNAME
FROME  EMPLOYEE
WHERE NOT EXISTS
(
    (SELECT PNUMBER
     FROM PROJECT as t1
     left join Works as t2 on t1.PNUMBER = t2.PNO and t2.SSN = t2.ESSN
     WHERE DNUM = 5 and t2.PNO is NULL
);
0
 

Author Comment

by:dienbaquan2nd
ID: 26112629
@angelIII: The data and the script are not in English, the query was posted here has been translated back to English.

I've attached the SQL script (not English - csdlcongty) and the data tables image (English - company). Please use the number in the image to collate with the data in the script.
csdlcongty.png
0
 

Author Comment

by:dienbaquan2nd
ID: 26112653
Here the script, sorry for separate it into another post.
CsdlCongty.sql
0
 

Author Comment

by:dienbaquan2nd
ID: 26112681
@Sharath_123: It returns all employees.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 26112920
I now understand the problem.
your initial query does not correlate the subquery to the main query, actually.

this should work, assuming you want all employees that do not work on a project for department #5
SELECT A.FNAME, A.LNAME
FROM EMPLOYEE A
WHERE NOT EXISTS
(
   SELECT NULL
   FROM WORKS_ON W
   JOIN PROJECT P
     ON P.PNUMBER = B.PNO
    AND P.DNUM = 5
  WHERE W.ESSN = E.SSN
);

Open in new window

0
 

Author Closing Comment

by:dienbaquan2nd
ID: 31668941
Perfect.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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

569 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