Query on sql query

Question

Query on sql query

Navigation

#1 by (6 votes)
#2 by (3 votes)
#3 by (-2 votes)

4

I have a question. I think it's not very complex, but I could not solve it.

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 that returns the rows to my system only if all rows are Active = true, so if you have any false rows, will not return any line.

Thus: If all rows are true in my query, return all rows. If it is false, you should not return any.

sql sql-select sql-server tsql

asked by anonymous 28.11.2017 / 23:55

3 answers

Turn text into italic on hover! What is the difference between the: nth-child and the: nth-of-type?

score 6 · Answer 1

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

There are a few ways. 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');

Functional Demonstration of the Code, by Leo Caracciolo

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

Ifyouhaveanyfalse,

shouldnotreturnany.

score 3 · Answer 2

Deriving from the @ JoséDiz idea, you could do a joining to the left of the table with itself, putting the same clinic identifier in the join clause and T2 being with T2.ativo = 'FALSE' . Once this join is made, if there is any data of T2 being returned in any of its columns, then I should not print this line. The advantage of this method is that if it appears on a line, then it should appear on ALL lines of the same clinic. So, just check for the nullability of T2.clinicaId in WHERE is enough to print all lines or no line.

On performance, I have my doubts. I always think that subqueries get in the way (this generalization is bad). Now about reading, for sure the @ 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

score -2 · Answer 3

-2

Just add one more filter:

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

29.11.2017 / 00:00