Bring records from one table according to another's where

Question

Bring records from one table according to another's where

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

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

There is the table called dbo.FISICA and another call dbo.MATRICULA , in the dbo.FISICA table there are all the "clients" of the system, with or without registration, and in the dbo.MATRICULA table are the registrations of these clients, each client may have one or more registrations, like this:

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 the students who only have a registration with status 0, so in this case my query should bring just 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

Of course he brought me the students who have 0 status registration, and not who only have 0 status status.

sql query

asked by anonymous 07.06.2018 / 01:04

2 answers

1

Add the clause to return only those matrices that do not have enrollments 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)

07.06.2018 / 01:28

Pass parameters (including an image) to PHP with Axios using formData How does this if / else work with "?" and ":"?

score 1 · Accepted Answer

One way to do this is to count how many statuses of the face are different from 0 and filter only those with 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 in SqlFiddle .