SQL Get All Results That Relate To All Keys

Question

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?

See this similar question with a lot (more than 10) ways to do just this: [How to filter SQL results in a has-many-through relation](http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation) — ypercubeᵀᴹ, Jun 04 '12 at 17:15
The result of your first query will be two-column. And for the second one, i think you need to redesign your data model. you need one table for words and relationships. Then you query them with ease. Unless this is a homework i would highly recommend changing the data model. — Adam, Jun 04 '12 at 17:15
@adam, I do have a separate table for the words, this is just an example I thought would be easier to understand. — Howard Zoopaloopa, Jun 04 '12 at 17:17
If you have all connections stored twice (meaning: `(dog, cat)` besides `(cat, dog)`), then most of your queries will be more simple. — ypercubeᵀᴹ, Jun 04 '12 at 17:25
@ypercube, so when I add the relationship I should just make two rows? then I could just query for column A instead of using OR column b? — Howard Zoopaloopa, Jun 04 '12 at 17:27
Yes, your inserts would be inserting 2 rows. Then you won't need `OR` in the queries. — ypercubeᵀᴹ, Jun 04 '12 at 17:29

ypercubeᵀᴹ · Accepted Answer · 2012-06-04T17:47:02.610

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' ;

ypercube, I'm definitely going to heed your suggestion and add two rows per insert. — Howard Zoopaloopa, Jun 04 '12 at 17:41

score 0 · Answer 2 · answered Jun 04 '12 at 17:12

0

You can use the INTERSECT command in SQL to find the intersection of several queries; i.e., returning what's common to all of them.

e.g.:

select field1 from table1 where id = 5
intersect
select field1 from table2 where id < 5

answered Jun 04 '12 at 17:12

Xophmeister

8,884
4
44
87

Interesting. I'm running an intersect in PHP from queries for each keyword, I figured handling it all in SQL would be faster. I'll look into this. – Howard Zoopaloopa Jun 04 '12 at 17:20
MySQL has not implemented `INTERSECT`. – ypercubeᵀᴹ Jun 04 '12 at 17:29

score 0 · Answer 3 · answered Jun 04 '12 at 17:38

As you explained in your comment, you have 2 tables. I assumed the first table is called words with two columns: id and word. And the relationship table contains id and id2 foreign keys to words table.

Then you could try something like this:

select distinct word from words w
where 
(exists 
(select * from table_word_relationships r 
inner join word w2 on w2.id = r.id2 
inner join word w3 on w3.id = r.id 
where (w.id = r.id1 and w2.word = "CAT") or (w.id = r.id2 and w3.word = "CAT"))
and
(exists 
(select * from table_word_relationships r 
inner join word w2 on w2.id = r.id2 
inner join word w3 on w3.id = r.id 
where (w.id = r.id1 and w2.word = "DOG") or (w.id = r.id2 and w3.word = "DOG"))

(Not tested)

SQL Get All Results That Relate To All Keys

3 Answers3