mysql - SQL Get All Results That Relate To All Keys -

i've got table relates words each other.

table_word_relationships +-------------+-------------+ |   word_a    |  word_b     | +-------------+-------------+ |   cat       |   dog       | +-------------+-------------+ |   cat       |   rabbit    | +-------------+-------------+ |   owl       |   cat       | +-------------+-------------+ |   bird      |   dog       | +-------------+-------------+ |   dog       |   banana    | +-------------+-------------+ |   banana    |   cat       | +-------------+-------------+ 

i need run 2 types of queries on it. 1 easy. give me words relate word "dog"

"select * table_word_relationships word_a = 'dog' or word_b = 'dog'"; 

the result be:

cat bird banana 

the second 1 one having trouble with... need query words have relationship of words in array of words...

so, in english "give me words have relationship word "dog" , have relationship word "cat" results be:

banana 

because banana word relates both keywords. proper type of sql statement this?

query 1 should be:

select word_b word table_word_relationships  word_a = 'dog'   union distinct  select word_a  table_word_relationships  word_b = 'dog' ; 

query 2 gets quite complicated because store every connection in 1 row only. 1 possible way:

select     dog.word   ( select word_b word     table_word_relationships      word_a = 'dog'       union distinct      select word_a      table_word_relationships      word_b = 'dog'    ) dog    join    ( select word_b word     table_word_relationships      word_a = 'cat'       union distinct      select word_a      table_word_relationships      word_b = 'cat'    ) cat    on cat.word = dog.word ; 

---

both queries simpler if storing connections in 2 rows, both (cat, dog) , (dog, cat).

query 1 then:

select word_b word table_word_relationships  word_a = 'dog' ; 

query 2:

select     dog.word_b word      table_word_relationships dog   join      table_word_relationships cat       on cat.word_b = dog.word_b         dog.word_a = 'dog'    ,        cat.word_a = 'cat' ;