sql, checking if a value appears more then 2x in a table

Question

if i have a schema that goes somthing like this

peoples(personNumber, etc) jobs(personNumber, jobNumber)

and a person can have more then one job, how can a write a query that tells me if a person has less then two jobs

select personNumber from peoples
where personNumber not in
(
    select personNumber from jobs
    where ??personNumber appears two times or more in jobs??
);

thanks to anyone who takes time to help

Answer 1

Use:

SELECT p.*
  FROM PEOPLES p
 WHERE NOT EXISTS(SELECT NULL
                    FROM JOBS j
                   WHERE j.personnumber = p.personnumber
                  HAVING COUNT(DISTINCT j.jobnumber) > 1)

Answer 2

select personNumber from
(
    select personNumber,count(*) as numJobs from jobs group by personNumber
) as jobCounts where numJobs<2

Answer 3

The easiest way to do this is to use the aggregate function COUNT() with a GROUP BY statement.

SELECT personNumber
FROM jobs
GROUP BY personNumber
HAVING count(*) < 2

Avoid the subquery that a few others have posted, it may be significantly slower depending on your database's optimizer. If you want to ensure that you're only seeing job entries corresponding to the peoples table, you should be using a foreign key.