How to select more than 2 matches?

Question

I'm having trouble thinking this one out. Input is greatly appreciated. Content is always changing, but here is an example of what I'm trying to do.

STORY 1 (keywords: 5, 9, 17, 18, 25, 22)

STORY 2 (keywords: 5, 25, 16, 19, 32)

STORY 3 (keywords: 1, 9, 17)

STORY 4 (keywords: 25, 22, 16, 3, 17)

Now, I'm querying based on STORY 1. I'd like to output any stories that share >= 2 keywords with STORY 1.

For this example, my code should output:

STORY 1

STORY 2

STORY 4

STORY 2 shares keywords 5, 25

STORY 4 shares keywords 25, 22, 17

STORY 3 is not outputted simply because it only shares 1 keyword with STORY 1

Any ideas?

Answer 1

Something like (not tested)

SELECT k2.story_id
FROM keywords k1 
     JOIN keywords ks ON k1.keyword = k2.keyword AND k1.story_id < k2.story_id
GROUP BY 
     k1.story_id
WHERE 
     k1.story_id = @story_id
HAVING COUNT(*) > 1

might work for you.

Answer 2

You can do it from SQL as Unreason showed, it is also very easy to do from PHP. Just make every Story an array of keywords and use array_intersect(story_1, story_n). You keep result arrays having more than two items.