Doubt about sql query

Question

I have a question. I believe it is nothing complex, but I could not solve.

For example, I have this query:

select * from Paciente where ClinicaID = 3

Your result would be these 3 records:

NomePaciente  HoraAtendimento   Ativo  ClinicaID
Teste2          9:30:00         FALSE   3
Teste3           9:00:00        TRUE    3
Teste4          9:00:00         TRUE    3

I need a query to return the lines to my system only if all the lines are Active = true, so if you have any false lines, will not return me any line.

So: If all lines are true in my query, return all lines. If you have any false ones, you should not return any.

Answer 1

If all lines are true in my query, return all lines. If you have any false, you should not return any.

There are some forms. Here’s one that uses the EXISTS function.

-- código #1
SELECT P.NomePaciente, P.HoraAtendimento, P.ClinicaID, P.Ativo
  from Paciente as P
  where P.ClinicaID = 3
        and P.Ativo = 'TRUE'
        and not exists (SELECT * 
                          from Paciente as P2
                          where P2.ClinicaID = P.ClinicaID
                                and P2.Ativo = 'FALSE');

---

Demonstration of the functioning of the code, by Leo Caracciolo

If all lines are true in my query, return all lines.

If you have any false,

shall not return any.

Answer 2

Deriving from the idea of @Josédiz, you could make a junction to the left of the table with itself, putting in the junction clause the same clinic identifier and T2 be with T2.ativo = 'FALSE'. Made this junction, if there is any data of T2 being returned in any of your columns, so I should not print that row. The advantage of this method is that if it appears in a line, then it should appear in ALL the lines of the same clinic. Therefore, only check for the nullity of T2.clinicaId in the WHERE is enough to print all lines or no line.

About performance, I have my doubts. I always think that Queries get in the way (this generalization is bad). Now, about reading, surely @Josédiz is easier to read.

SELECT T1.NomePaciente, T1.HoraAtendimento, T1.ClinicaID, T1.Ativo
  from Paciente as T1 left join
    Paciente as T2 on (T1.clinicaId = T2.clinicaId AND T2.ativo = 'FALSE')
  where T1.ClinicaID = 3
        and T1.Ativo = 'TRUE'
        and T2.clinicaId IS NULL

Answer 3

Just add one more filter:

SELECT * FROM Paciente WHERE ClinicaID = 3 AND Ativo = TRUE