mysqlsqlsql-match-all

SQL Get All Results That Relate To All Keys


I've got a table that relates words to each other.

table_word_relationships
+-------------+-------------+
|   WORD_A    |  WORD_B     |
+-------------+-------------+
|   cat       |   dog       |
+-------------+-------------+
|   cat       |   rabbit    |
+-------------+-------------+
|   owl       |   cat       |
+-------------+-------------+
|   bird      |   dog       |
+-------------+-------------+
|   dog       |   banana    |
+-------------+-------------+
|   banana    |   cat       |
+-------------+-------------+

I need to run two types of queries on it. One is easy. Give me all the words that relate to the word "dog"

"SELECT * FROM table_word_relationships WHERE WORD_A = 'dog' OR WORD_B = 'dog'";

The result would be:

cat
bird
banana

The second one is the one I am having trouble with... I need to query for all words that have a relationship with ALL of the words in an array of words...

So, in English I would say "Give me all the words that have a relationship with the word "dog" and ALSO have a relationship with the word "CAT" The results would be:

banana

Because banana is the only word that relates to both keywords. What would be the proper type of SQL statement for this?


Solution

  • Query 1 should be:

    SELECT word_b AS word
    FROM table_word_relationships 
    WHERE word_a = 'dog' 
    
    UNION DISTINCT
    
    SELECT word_a 
    FROM table_word_relationships 
    WHERE word_b = 'dog' ;
    

    Query 2 gets quite complicated because you store every connection in one row only. One possible way:

    SELECT
        dog.word
    FROM
      ( SELECT word_b AS word
        FROM table_word_relationships 
        WHERE word_a = 'dog' 
    
        UNION DISTINCT
    
        SELECT word_a 
        FROM table_word_relationships 
        WHERE word_b = 'dog' 
      ) AS dog
    
      JOIN
    
      ( SELECT word_b AS word
        FROM table_word_relationships 
        WHERE word_a = 'cat' 
    
        UNION DISTINCT
    
        SELECT word_a 
        FROM table_word_relationships 
        WHERE word_b = 'cat' 
      ) AS cat
    
      ON cat.word = dog.word ;
    

    Both your queries will much simpler if you are storing all connections in two rows, both (cat, dog) and (dog, cat).

    Query 1 then:

    SELECT word_b AS word
    FROM table_word_relationships 
    WHERE word_a = 'dog' ;
    

    Query 2:

    SELECT
        dog.word_b AS word
    FROM 
        table_word_relationships AS dog
      JOIN 
        table_word_relationships AS cat
          ON cat.word_b = dog.word_b 
    WHERE 
          dog.word_a = 'dog' 
      AND 
          cat.word_a = 'cat' ;