CODEWITHSUNDEEP
mysql
Howard Zoopaloopa
Howard Zoopaloopa

Reputation: 3822

MySql Select Where Column Value Is Unique

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?

Upvotes: 1

Views: 97

Answers (6)

Beto
Beto

Reputation: 333

Easier and solve your problem:

SELECT distinct name FROM people_jobs WHERE job = "Actor"

Upvotes: -1

fthiella
fthiella

Reputation: 49089

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')

Upvotes: 0

Sergey Mazur
Sergey Mazur

Reputation: 41

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

Upvotes: 3

mmccaff
mmccaff

Reputation: 1281

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

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

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;

Upvotes: 1

Marc B
Marc B

Reputation: 360842

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.

Upvotes: 3

Related Questions