CODEWITHSUNDEEP
mysqlsql
Tyler Perry
Tyler Perry

Reputation: 46

Fetching a distinct column only if duplicates columns have the same data

I have this type of table:

+-----TABLE1----+
| Name, Boolean |
| A   , 0       |
| B   , 0       |
| B   , 0       |
| A   , 1       |
| C   , 1       |
| D   , 0       |
| C   , 0       |
| A   , 0       |
| A   , 1       |
| B   , 0       |
| D   , 0       |
+--------------+

I want to select distinct all names that their duplicates booleans are 0 aswell. So the result will be:

+---------------+
| RESULT:       |
| Name          |
| B             |
| D             |
+---------------+

Because A and C contains boolean also of "1" so they wont be fetched

I can do:

SELECT DISTINCT name, MAX(boolean) as boolean FROM table1 GROUP BY name;

But what condition I need to use if I want to fetch only the results that their max(boolean) are 0?

I can't use two select statements inside one query because we are talking about big data database.. so this solution is not an option in my case:

SELECT DISTINCT t1.name FROM table1 t WHERE t1.name NOT IN (SELECT DISTINCT t2.name FROM table1 t2 WHERE t2.boolean = 1);

Couldn't think of an option when using "JOIN" aswell.

Any solutions?

Thanks in advance! :)

Upvotes: 0

Views: 20

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You can use aggregation:

select name
from table1 t
group by name
having min(boolean) = max(boolean);

Upvotes: 1

Related Questions