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

2662 views mysql
-3

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

answered question

group by, having

1 Answer

3

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.

posted this

Have an answer?

JD

Please login first before posting an answer.