CODEWITHSUNDEEP
mysqlsqldb2
Felix
Felix

Reputation: 75

SQL select only rows which have same column value

I need some hints.. I got a table which looks like that: 

id  fruit   delicious
1   apple   1
2   apple   0
3   apple   1
4   cherry  1
5   cherry  1
6   cherry  1
7   banana  0
8   banana  0
9   banana  0

For this table I want to create a statement which will only return the fruits which are completely delicious but not the fruits which are partially delicious. In this example only "cherry" should be returned.

Currently I just got this statement:

SELECT * FROM fruits WHERE delicious <> 0

but this would also return apple and banana.

I'm a bit clueless how I could archive that and looking forward to some hints.

EDIT: Thank you very much for your replys. Unfortunately the flag "delicious" is a char and not an integer

Upvotes: 1

Views: 98

Answers (4)

Christoph
Christoph

Reputation: 4401

Just look for those that have a min delicious value of 1

SELECT Id, Fruit FROM fruits
GROUP BY Id, Fruit
HAVING min(delicious) = 1

Upvotes: 2

Fantasy Wang
Fantasy Wang

Reputation: 1

This is not the best way, but can use in many situations.

SELECT DISTINCT fruit 
FROM fruits 
WHERE NOT fruit IN (
  SELECT DISTINCT fruit 
  FROM fruits 
  WHERE delicious = 0
);

Upvotes: 0

Enrique Zavaleta
Enrique Zavaleta

Reputation: 2108

This also works

SELECT fruit, sum(delicious) AS TotalScore, count(fruit) AS TotalFruits
FROM ForgeRock
GROUP BY fruit
HAVING TotalScore = TotalFruits

Upvotes: 0

juergen d
juergen d

Reputation: 204756

Group by the fruit and take only those having zero records with delicious = 0

SELECT fruit 
FROM fruits 
GROUP BY fruit
HAVING sum(case when delicious = 0 then 1 else 0 end) = 0

Upvotes: 2

Related Questions