Best unofficial Apache Server developers community
Username
Forgot password?
Sign in with Twitter account
Sign in with Facebook account

Most common tags amongst selected table items?

0

37 views

I have a table similar to below

Questions table:

Qid  Tags
---  ---------
1    SQL
1    DATABASE
1    ALGORITHM
2    ALGORITHM
2    TAGS
3    SQL
3    SYNTAX
3    DATABASE

The following

SELECT * FROM Question table WHERE TAGS='SQL'

will display:

Qid  Tags
---  ----
1    SQL
2    SQL
3    SQL

I'm looking for a query which will display the highest related question based on similar tags.

So if I looked up Question table - Qid=1. The result would be Qid 3, as Qid 1 and Qid 3 have 2 similar terms, but Qid 2 only as 1 similar tag to Qid 1. If i looked up Qid 2 it would return Qid 1 as it they share 1 tag and Qid 2 and 3 share no tags. And If i looked up Qid 3 it would return Qid 1 as it as Qid 1 has 2 tags that Qid 3 has, but Qid 2 has no tags that Qid 3 has.

Thanks for any help.

asked June 20, 2011 12:26 pm CDT
posted via StackOverflow

2 Answers

0
 

Something along these lines maybe?

SELECT Qid, COUNT(*) AS TagsInCommon FROM Questions
WHERE Tags IN (
  SELECT DISTINCT Tags FROM Questions WHERE Qid=1
)
GROUP BY Qid
ORDER BY TagsInCommon DESC

I'm not sure about the field names but you get the idea...

answered June 20, 2011 1:23 pm CDT
0
 
declare @Qid int
set @Qid = 1

select top 1 Q2.Qid, COUNT(*) from Questions Q1
inner join Questions Q2 on Q1.Tags = Q2.Tags
where Q1.Qid = @Qid and Q2.Qid <> @Qid
group by Q2.Qid
order by COUNT(*) desc

answered June 20, 2011 1:23 pm CDT

Your answer

Join with account you already have


Sign in with Twitter account
Sign in with Facebook account
Sign in with Google Friend Connect

Preview
Similar questions