[RESOLVED] Using SQL "in"

[chronomantic]

Hey everyone! I have a quick question, and I'm thinking this isn't possible but I just have to ask 🙂 I have a blog and I want to create a 'related posts' section for it. Here are my three tables:

blog_posts
tags
tags_to_post

I want to order the related posts based on how many tag matches there are to the current post, so a post matching all 5 tags of the current post would score higher in relevancy than a post matching just 3 of the 5 tags.

So, I first get the post's tags:

SELECT tag_id FROM tags WHERE blog_post = 777

If I create a list out of this and use the SQL 'IN' command can I order the results by a COUNT of how many tags matched? Or something? i.e... :

SELECT * FROM blog_posts, tags_to_post, WHERE tags_to_post.post_id = bog_posts.post_id AND tags_to_post.tag_id IN (value1, value2, ... ) ORDER BY number_of_matches DESC LIMIT 10

Thanks for the help!

[Bjom]

first get rid of the asterisk (*) and select only the fields that are of interest. next do something along

select post_id, count(tag_id) from .... group by tag_id

[chronomantic]

Very cool, thanks for the help!

In case anyone's interested, here is the query that works for me.. I'm grouping on post title rather than tag:

SELECT post_id, post_title, count(tag_id) AS commontags
FROM blog_posts, tags_to_post
WHERE tags_to_post.post_id = blog_posts.post_id AND tag_id IN (1,2,3,4,etc..)
AND blog_posts.post_id != 7777
GROUP BY post_title ORDER BY commontags DESC LIMIT 10

This gets a list of stories (excluding the story we're finding the related posts of) that have tags in common with those listed in the IN clause and orders them in descending order, with a limit of 10 results.