CODEWITHSUNDEEP
mysql
user1856596
user1856596

Reputation: 7233

Find those rows with only one value in another column?

This is my table

column1          column2 (varchar)
--------------------------
foobar           0
foobar           1
whatever         0
whatever         0
whatever         0

What I want is to get all those values from column1 that have only zeros in column 2. In this case I only want "whatever", because "foobar" has values 0 and 1. I tried with a join, but failed. How could I solve that?

Thanks!

Upvotes: 0

Views: 45

Answers (3)

georgecj11
georgecj11

Reputation: 1637

I prefer using join over subquery which often gives better result

SELECT column1
FROM table1 t1
INNER JOIN table1 t2
 ON t2.column1=t1.column1
-- GROUP BY column1

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Here is the simplest way:

select column1
from table t
group by column1
having sum(column2 <> '0') = 0;

That is, count the number of values in column2 that are not 0. And make sure there are no such rows.

Upvotes: 1

niyou
niyou

Reputation: 873

Try this:

SELECT column1
FROM table1 t1
WHERE (SELECT SUM(column2)
       FROM table1 t2
       WHERE t2.column1=t1.column1
       GROUP BY column1) = 0
--GROUP BY column1

if you want only one row as result uncomment last row

Upvotes: 2

Related Questions