mysql 通过主键搜索,在MySQL中查询以基于主键查找不同的状态

My question is regarding a MySQL query that I am trying to write. I have written some psuedo-code to help illustrate what query I am trying to write:

SELECT *

FROM persons AS p

INNER JOIN person_info AS pi

ON p.person_id = pi.person_id

WHERE status MAY INCLUDE lost, missing, or found

WHAT person_id has no instances of the found status

I'd like to know for each person_id (which can have multiple statuses), which do not have any instance of the status "found." I'm not concerned with just the records of lost and missing. I want to find the unique cases where there is no "found" status based on each unique, distinct person_id.

解决方案

This is as far as I took it @sgeddes. In writing it I realized it just makes peoples eyes glaze over.

SQL NOT IN () danger

create table mStatus

( id int auto_increment primary key,

status varchar(10) not null

);

insert mStatus (status) values ('single'),('married'),('divorced'),('widow');

create table people

( id int auto_increment primary key,

fullName varchar(100) not null,

status varchar(10) null

);

Chunk1:

truncate table people;

insert people (fullName,status) values ('John Henry','single');

select * from mstatus where status not in (select status from people);

** 3 rows, as expected **

Chunk2:

truncate table people;

insert people (fullName,status) values ('John Henry','single'),('Kim Billings',null);

select * from mstatus where status not in (select status from people);

no rows, huh?

Obviously this is 'incorrect'. It arises from SQL's use of three-valued logic,

driven by the existence of NULL, a non-value indicating missing (or UNKNOWN) information.

With NOT IN, Chunk2 it is translated like this:

status NOT IN ('married', 'divorced', 'widowed', NULL)

This is equivalent to:

NOT(status='single' OR status='married' OR status='widowed' OR status=NULL)

The expression "status=NULL" evaluates to UNKNOWN and, according to the rules of three-valued logic,

NOT UNKNOWN also evaluates to UNKNOWN. As a result, all rows are filtered out and the query returns an empty set.

Possible solutions include:

select s.status

from mstatus s

left join people p

on p.status=s.status

where p.status is null

or use not exists