INNER JOIN on the same table counting occurrence words

Question

http://sqlfiddle.com/#!2/e6382

id id_news word 1 6 superman 2 6 movie 3 6 review 4 6 excellent 5 7 review 6 7 guardians of the galaxy 7 7 great 8 8 review 9 8 superman 10 8 movie 11 8 great 

I have a little problem, I'm trying to connect different news using words with a threshold setting, in the given example id_news 6 should be associated with 8 , but not with 7 , since 7 has only 2 common words, and I only want to find those that have There are at least 3 words.

Answer 1

This will bring you closer to what you need:

  SELECT wa1.id_news id, wa2.id_news related FROM word_analysis wa1 JOIN word_analysis wa2 ON wa2.id_news != wa1.id_news AND wa2.word = wa1.word GROUP BY wa1.id_news, wa2.id_news HAVING COUNT(*)>2 ORDER BY wa1.id_news, wa2.id_news 

If you do not need a reverse relationship:

  SELECT wa1.id_news id, wa2.id_news related FROM word_analysis wa1 JOIN word_analysis wa2 ON wa2.id_news > wa1.id_news AND wa2.word = wa1.word GROUP BY wa1.id_news, wa2.id_news HAVING COUNT(*)>2 ORDER BY wa1.id_news, wa2.id_news 

If you want to explore only one wa1.id_news (6):

  SELECT wa2.id_news related FROM word_analysis wa1 JOIN word_analysis wa2 ON wa2.id_news != wa1.id_news AND wa2.word = wa1.word WHERE wa1.id_news = 6 GROUP BY wa1.id_news, wa2.id_news HAVING COUNT(*)>2 ORDER BY wa2.id_news 

If you want to investigate only one relation (6-> 8), where the result means interconnected and no result is related:

  SELECT 1 FROM word_analysis wa1 JOIN word_analysis wa2 ON wa2.id_news = 8 AND wa2.word = wa1.word WHERE wa1.id_news = 6 GROUP BY wa1.id_news, wa2.id_news HAVING COUNT(*)>2 

Answer 2

Try this self-connect:

 SELECT wa1.id_news id_news_1, wa2.id_news id_news_2, count(wa2.word) cnt_words FROM word_analysis wa1 INNER JOIN word_analysis wa2 ON wa1.id_news <> wa2.id_news AND wa1.word = wa2.word GROUP BY wa1.id_news, wa2.id_news HAVING count(wa2.word) >= 3 ORDER BY wa1.id_news, wa2.id_news; 

SQL Fiddle Demo