CODEWITHSUNDEEP
mysqlsqloracle-database
Soumav
Soumav

Reputation: 403

Selecting Rows for a particular ID where columns corresponding to that have only mentioned set of values

I have a table as follows:

ID  | Value  | Other Columns...........| .......
-----------------------------------------------
ID1 | Value1 | ....................... | .......
ID1 | Value2 | ....................... | .......
ID2 | Value3 | ....................... | .......
ID3 | Value4 | ....................... | .......
ID3 | Value4 | ....................... | .......
ID3 | Value5 | ....................... | .......
ID4 | Value4 | ....................... | .......
ID4 | Value5 | ....................... | .......
ID5 | Value2 | ....................... | .......
ID5 | Value4 | ....................... | .......
ID5 | Value5 | ....................... | .......

The Value column can have 5 types of values : Value1 through Value5. I need to write a query where I should get all IDs where Values are only Value4 and Value5. Example: ID3 and ID4. As I am new to SQL, how should I go about it in an optimized way?

Upvotes: 0

Views: 26

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

You can use group by and having. A simple way is:

select id
from t
group by id
having sum(case when value = 'value4' then 1 else 0 end) > 0 and
       sum(case when value = 'value5' then 1 else 0 end) > 0 and
       sum(case when value not in ('value4', 'value5') then 1 else 0 end) = 0;

The conditions are counting how many times the value is each of the values for a given id. The > 0 says that at least one row has the value. The = 0 says that no rows with the id have the value.

Upvotes: 2

Sumanta Mukhopadhyay
Sumanta Mukhopadhyay

Reputation: 13

Just I am giving you a rough idea of the query .

Select * from table where value is value or value 4

Upvotes: 0

Related Questions