SQL find single value from duplicates with certain condition

Question

I realize this is probably painfully simple. Just getting late and need an explanation.

I have the table:

mysql> SELECT * FROM employee_expert;
+------+---------+
| enum | package |
+------+---------+
| E246 | Excel   |
| E246 | MySQL   |
| E246 | Python  |
| E246 | Word    |
| E403 | Java    |
| E403 | MySQL   |
| E892 | Excel   |
| E892 | PHP     |
| E892 | Python  |
+------+---------+
9 rows in set (0.00 sec)

And I need to find the enum of the tuple NOT Python Result should be E403 since its the only one not with Python.

Tried

mysql> SELECT enum FROM employee_expert WHERE package != "Python" GROUP BY enum;
+------+
| enum |
+------+
| E246 |
| E403 |
| E892 |
+------+
3 rows in set (0.00 sec)

But, obviously, it just returned all the enums...

Strawberry · Accepted Answer · 2014-12-04 18:16:32Z

2

One option is the not in operator:

SELECT DISTINCT enum
FROM   employee_expert
WHERE  enum NOT IN (SELECT enum 
                    FROM   employee_expert
                    WHERE  package = 'Python')

edited Dec 4, 2014 at 18:16

Strawberry

34k14 gold badges43 silver badges58 bronze badges

answered Dec 4, 2014 at 18:07

Mureinik

316k54 gold badges402 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Wilf Engel Over a year ago

YES. Thank you very much. Had tried the NOT IN but not with a subquery.

Strawberry Over a year ago

(On a properly indexed table, this is the slowest of the 3 solutions provided - just saying)

StuartLC · Accepted Answer · 2014-12-04 18:06:36Z

2

A NOT IN will exclude a subset of data with a specific exclusion criteria:

SELECT DISTINCT(enum)
FROM employee_expert
WHERE enum NOT IN
(SELECT enum FROM employee_expert WHERE package = 'Python');

Distinct will exclude duplicates. Sql Fiddle here

answered Dec 4, 2014 at 18:06

StuartLC

108k18 gold badges225 silver badges295 bronze badges

1 Comment

Wilf Engel Over a year ago

Thanks for the help. Can only accept 1. Couldn't tell if you or Mureinik was first to answer.

Fabien TheSolution · Accepted Answer · 2014-12-04 18:16:35Z

2

I think that a NOT EXISTS can be faster than NOT IN

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE employee_expert
    (`enum` varchar(4), `package` varchar(6))
;

INSERT INTO employee_expert
    (`enum`, `package`)
VALUES
    ('E246', 'Excel'),
    ('E246', 'MySQL'),
    ('E246', 'Python'),
    ('E246', 'Word'),
    ('E403', 'Java'),
    ('E403', 'MySQL'),
    ('E892', 'Excel'),
    ('E892', 'PHP'),
    ('E892', 'Python')
;

Query 1:

SELECT DISTINCT enum 
FROM employee_expert ee1
WHERE NOT EXISTS 
(SELECT 1 
 FROM employee_expert ee2 
 WHERE ee1.enum = ee2.enum AND ee2.package = 'Python')

Results:

| ENUM |
|------|
| E403 |

answered Dec 4, 2014 at 18:16

Fabien TheSolution

5,0501 gold badge20 silver badges31 bronze badges

Comments

Strawberry · Accepted Answer · 2014-12-04 18:14:57Z

1

SELECT DISTINCT x.enum 
           FROM employee_expert x 
           LEFT 
           JOIN employee_expert y 
             ON y.enum = x.enum 
            AND y.package = 'Python' 
          WHERE y.enum IS NULL;

answered Dec 4, 2014 at 18:14

Strawberry

34k14 gold badges43 silver badges58 bronze badges

Collectives™ on Stack Overflow

SQL find single value from duplicates with certain condition

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related