SQL query to get all the distinct words in a table where the related id is in an array

user_961612 0
Viewed 48

I am in over my head with this sql query.

I need to get all the distinct words in a table where the element_id is in an array.

 word | element_id
------+-----------
 cat  | 1 
 dog  | 1 
 cat  | 2 
 dog  | 2 
 rat  | 1 
 rat  | 3

I would like to find all the words that have the element_id of 1 and 2.

In this case I would like to return cat and dog but not rat.

I tried this, but it doesn't work.

select distinct word from words where element_id = 1 and element_id = 2

mysql sql
2 Answers

You can use group by and having:

select word
from words
where element_id in (1, 2)
group by word
having count(*) = 2;

This assumes that your table doesn't have duplicate rows.

If performance is an issue, then:

select w.word
from words w
where w.element_id = 1 and
      exists (select 1 from words w2 where w2.word = w.word and w2.element_id = 2);

This can take advantage of an index on words(word, element_id) and words(element_id) (two separate indexes).

user_11440350

One option would be using avg and sum aggregate functions within a grouping select statement that includes having clause as :

select word
  from words
 group by word
having avg(nvl(element_id,0)) = 1.5 and sum(nvl(element_id,0)) = 3;

assuming element_id is a nullable integer column.

Or 

select word
  from words
 group by word
having avg(element_id) = 1.5 and sum(element_id) = 3;

as element_id is a non-nullable integer column.

P.S. In this way, no need to write the ids explicitly.

user_58413060
Related