Bring the records from one table according to the Where from another

Question

I am new in the database area and I came across a situation that I do not know how to solve. Even though it seems (at least, rs) simple.

Exists the table called dbo.FISICA and another call dbo.MATRICULA, on the table dbo.FISICA all "customers" of the system, whether or not they have registration, and in the table dbo.MATRICULA these customers' registrations remain, each customer can own one or more registrations, thus:

TABELA dbo.FISICA:

CODIGO  NOME     SEXO   DATANASC      CPF   
------  ----     ----   --------      ---
1       Otavio   M      22/05/1994    111.111.111-11
2       Luis     M      21/04/1993    222.222.222-22

TABELA dbo.MATRICULA:

CODIGO   FISICA   CURSO   STATUS
------   ------   -----   ------
1        1        3       0
2        1        4       2
3        1        5       4
4        2        3       0

My intention is to make a query that brings students who have only enrollment status 0, so in this case in my query should bring only o Luis.

Here is my query:

select
F.NOME,
M.STATUS
from dbo.MATRICULA M
Left join dbo.FISICA F on F.CODIGO = M.FISICA
Left join dbo.TURMAS T on T.CODIGO = M.TURMA_REGULAR
WHERE M.STATUS = 0

And the result:

NOME    STATUS
----    ------
Otavio  0
Luis    0

Because he obviously brought me students who have status 0 enrollment, and not who only have status 0 number plates.

Answer 1

Add the clause to return only matricules that do not have matricules with status other than 0

...
WHERE M.STATUS = 0
and not exists (select 1 from dbo.MATRICULA ma where ma.FISICA = M.FISICA and ma.STATUS <> 0)

Answer 2

One way to do it is to count how many status of the guy is different from 0 and filter only those who have 0, see:

SELECT F.nome, 
       M.status 
FROM   matricula M 
       JOIN fisica F 
         ON F.codigo = M.fisica 
WHERE  (SELECT Count(*) 
        FROM   matricula 
        WHERE  fisica = F.codigo 
               AND status <> 0) = 0 

See working on Sqlfiddle.