query with several mysql statuses

Navigation
2

I have a question with a mysql query.

What I need to get are the nombres that have only the estado = 1 . As you can see in the table, the nombre "pepe" has status "1 and 0", is there any way to exclude it? 

    tabla personas

    id |estado|nombre
     1 |  1   |pepe
     2 |  0   |pepe
     3 |  1   |juan
     4 |  1   |diego
     5 |  0   |alex

What I have is this 

SELECT 
    * 
FROM 
    personas 
WHERE 
    estado in (SELECT 
                  DISTINCT estado 
               FROM 
                  personas 
               WHERE estado = 1)

but it shows me all those who have been 1 .. and I can not exclude those who have 0 and 1, I hope it is understood, thanks

    
asked by Renato 11.09.2017 в 16:17
source

3 answers

4

Use NOT EXISTS : 

SELECT *
FROM personas p
WHERE NOT EXISTS(SELECT 1 FROM personas
                 WHERE estado <> 1
                 AND nombre = p.nombre);
    
answered by 11.09.2017 / 16:23
source
3

Or you can use NOT IN : 

select *
from personas
where nombre not in
(
  select nombre from personas
  where estado = 0
)

SQLFiddle: link

    
answered by 11.09.2017 в 16:34
1

You already have a valid answer, I'll give you an alternative that is something different but it can be valid too: 

SELECT   id, (MIN(estado) && 1) AS estado, nombre
FROM     personas
GROUP BY nombre
HAVING   estado = 1;

The idea is that we are always going to be left with the state of least value that a person has, to only stay with the records whose result is 1 when doing a logical AND with 1: 

Y | 0 | 1
--+---+--
0 | 0 | 0
1 | 0 | 1

This query is based on the idea that estado is only going to have the values 1 or 0 (or that it will have different values but we only want to exclude those with a 0). So, the query will only return the records that either have 1, or that if they have more than one state, none of them is 0.

You can see it working (compared to the other answers) in this SQL Fiddle .

    
answered by 11.09.2017 в 17:42
Get img src="" when pressing image to insert in editor Background image HTML background