How to find the records with most common tags, like the related questions in StackOverflow?

Perhaps something like: select qt. Question_id, count(*) from question_tags qt where qt. Tag in ( select qt2.

Tag from question_tags qt2 where qt2. Question_id = 123 ) group by qt. Question_id order by 2 desc.

If you can guarantee that there are not duplicate tags for a question, then you can do the following: SELECT QT2. Question_id, COUNT(*) AS cnt FROM Question_Tags QT1 INNER JOIN Question_Tags QT2 ON QT2. Tag = QT1.

Tag AND QT2. Question_id QT1. Question_id WHERE QT1.

Question_id = @question_id GROUP BY QT2. Question_id ORDER BY cnt DESC If you can't guarantee uniqueness of tags within a question, then Tony Andrews' solution will work. S will work in any event, but you should compare performance on your system with this method if you can make the guarantee of uniqueness through constraints.

Sorry for the confusion. I'm asking the question for my own application. Assume we have 100 questions in a database, each question has several tags.

Let's say user is browsing a specific question, and we want to make the system to display the related questions on the page. The criteria for related question is they have most common tags. For example: Question 1 is tagged with AAA, BBB, CCC, DDD, EEE.

Question 2 is top 1 related because it also has all those 5 tags. Question 3 is top 2 related because it has only 4 or 3 tags that Questio1 has. ...... So my question is how to design the database and find out the questions that's related to Question 1 quickly.

Thank you very much.

I'm having the same issue just now – bodacious Jun 14 at 21:37.

Not entirely sure what you mean, but the Tags page lists tags in order of popularity (as in amount tagged). Edit: is this about SO or about your own application? If it is about your own app, remove the SO tag as it's kind of misleading.

Edit2: I'd say something like: SELECT * FROM `questions` WHERE `tag` LIKE '%tagname%' OR (looped for each tag) LIMIT 5,0 Where 5 is the maximum results you want to return (for at least some optimisation). Probably not the best solution, but I could see it working. You might also want to try a LIKE match using the title.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.