CODEWITHSUNDEEP
mysqlsql
Daniel Thomsen
Daniel Thomsen

Reputation: 1

SQL - select only one specific type

I would like to know if it's possible to select data with one exact statement.

Lets say I got 2 customers in my database, both of them got the value "1", but one of them has also a value called "2".

How can I get a result with only customer number 1? I'm interested to get all customers who only has the value "1", not "1", "2", "3" etc.

More specific description:

**Customer 1:**
Name: "Peter"
Value: "1"

**Customer 2:**
Name: "Chris"
Value: "1", "2", "3"

Now, I only want a result with Customer 1, who has the value "1"

Upvotes: 0

Views: 2327

Answers (2)

Darshan Mehta
Darshan Mehta

Reputation: 30809

You can use GROUP BY with COUNT, e.g.:

SELECT Name
FROM customer
WHERE Value = "1"
GROUP BY Name
HAVING COUNT(DISTINCT(Value)) = 1;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can use not exists:

select t.*
from t
where t.value = 1 and 
      not exists (select 1 from t t2 where t2.name = t.name and t2.value <> 1);

Upvotes: 1

Related Questions