CODEWITHSUNDEEP
sqldatabaseoracle-database
bZhang
bZhang

Reputation: 307

querying years that contains multiple fields in oracle db

i am trying to write a query in oracle db that helps me to select something that contains all 3 data, let me show an example Say I have a table 

ltr year
--------
'a' 2003 
'b' 2003 
'c' 2005
'a' 2003
'a' 2003
'j' 2008
'c' 2003

and I want to return the year(s) that contains at least one instance of the letters a, b, c. In here 2003 would be the only year that is returned.

So far I have

select distinct year 
from Data
where letter = 'a' or letter = 'b' or letter = 'c';

This will return years 2005 as well which isnt correct.. What am I missing? Any help is appreciated!

Upvotes: 1

Views: 45

Answers (1)

FuzzyTree
FuzzyTree

Reputation: 32392

Specify the letter you want in your where clause and use having count(distinct ltr) = 3 to get all years that have all letters at least once.

select year
from data
where ltr in ('a','b','c')
group by year
having count(distinct ltr) = 3

if you want years that only have letters 'a', 'b', 'c':

select year
from data
group by year
having count(case when ltr not in ('a','b','c') then 1 end) = 0

Upvotes: 1

Related Questions