MySql Select Where Column Value Is Unique

Question

My table people_jobs:

+--------+--------+
|  NAME  |  JOB   | 
+--------+--------+
|  John  |  Actor | 
+--------+--------+
|  Jane  | Driver | 
+--------+--------+
|  Bill  |  Actor | 
+--------+--------+
|  John  |  Cook  | 
+--------+--------+

I'm looking to select all names with the job actor where the name column would be unique. The desired query output here would be just Bill.

Something like:

SELECT name FROM people_jobs WHERE job LIKE "actor" AND COUNT(SELECT * FROM people_jobs WHERE name LIKE name) = 1;

This is apparently bad syntax and I couldn't get GROUP BY to work... Thoughts?

SELECT distinct name FROM people_jobs WHERE job='actor' Something like that? — CollinD
– CollinD, Commented Sep 14, 2015 at 21:27
That's because john and bill are both actors. You need to rephrase your question. I understand now you want to select all actors with names that are present exactly once in the table. — CollinD
– CollinD, Commented Sep 14, 2015 at 21:32

Marc B · Accepted Answer · 2015-09-14 21:35:37Z

3

Why a subselect?

SELECT name, COUNT(*) AS cnt
FROM people_jobs
WHERE job='Actor'
GROUP BY name
HAVING cnt = 1

Ok, now I see the problem. Try this instead:

SELECT name, SUM(JOB='Actor') AS actor_cnt, COUNT(*) as job_cnt
FROM people_jobs
GROUP BY name
HAVING (actor_cnt = 1) AND (job_cnt = 1)

This'll figure out how many people are actors, and count how many jobs they have, and return ONLY the people whose only job is acting.

edited Sep 14, 2015 at 21:35

answered Sep 14, 2015 at 21:26

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

4 Comments

Howard Zoopaloopa Over a year ago

I'm still getting both John and Bill back from this query.

Marc B Over a year ago

hmm. and now I see the problem. hang on. modified version coming right up.

Howard Zoopaloopa Over a year ago

Perfect! I was hoping the answer wasn't THAT easy.

Josh Whitlow Over a year ago

How would you convert this query to an update statement so that you were updating the name?

Sergey Mazur · Accepted Answer · 2015-09-14 21:50:24Z

3

select name
from people_jobs
where name in (
  select name
  from people_jobs
  group by name
  having count(name) = 1
) and job like 'actor'

This example is simple for understanding, but I like another one:

SELECT name
FROM people_jobs
GROUP BY name
HAVING COUNT(name) = 1 AND sum(job = 'Actor') = 1

edited Sep 14, 2015 at 21:50

answered Sep 14, 2015 at 21:33

Sergey Mazur

413 bronze badges

1 Comment

Nate Barbettini Over a year ago

Can you add some explanation on how this works? Otherwise, good answer!

Strawberry · Accepted Answer · 2015-09-14 21:36:36Z

1

SELECT DISTINCT x.* 
           FROM people_jobs x 
           LEFT 
           JOIN people_jobs y 
             ON y.name = x.name 
            AND y.job <> x.job 
          WHERE x.job = 'actor' 
            AND y.name IS NULL;

answered Sep 14, 2015 at 21:36

Strawberry

34k14 gold badges43 silver badges58 bronze badges

1 Comment

Howard Zoopaloopa Over a year ago

Thanks for your input, Strawberry. I got a useable solution below, but am definitely giving you an upvote.

mmccaff · Accepted Answer · 2015-09-14 21:40:06Z

1

SELECT name, COUNT(*) c 
FROM people_jobs 
WHERE job = 'Actor' 
GROUP BY name HAVING c = 1

edited Sep 14, 2015 at 21:40

answered Sep 14, 2015 at 21:33

mmccaff

1,2811 gold badge11 silver badges20 bronze badges

2 Comments

CollinD Over a year ago

Should this not be GROUP BY name?

mmccaff Over a year ago

Yes, that was a typo that I just caught. Fixed.

fthiella · Accepted Answer · 2015-09-14 21:54:54Z

0

I would use NOT EXISTS on this context:

SELECT name
FROM   people_jobs
WHERE  job = 'actor'
       AND NOT EXISTS (SELECT * FROM people_jobs pj
                       WHERE pj.name = people_jobs.name AND pj.job != 'actor')

answered Sep 14, 2015 at 21:54

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

Comments

Beto · Accepted Answer · 2015-09-14 22:05:16Z

-1

Easier and solve your problem:

SELECT distinct name FROM people_jobs WHERE job = "Actor"

answered Sep 14, 2015 at 22:05

Beto

3332 gold badges3 silver badges10 bronze badges

Collectives™ on Stack Overflow

MySql Select Where Column Value Is Unique

6 Answers 6

4 Comments

1 Comment

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

1 Comment

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related